Help with workday formula

[Damian_Turcios]

HI,IS THERE A FORMULA I CAN ADD IN A VIRTUAL COLUMN THAT COUNTS THE NUMBER OF SATURDAYS AND SUNDAYS BETWEEN TWO SPECIFIC DATES ?

[LeventK · Accepted solution]

@Damian_Turcios
Do you expect to count the starting and ending date as well if their weekday is Saturday and/or Sunday too?

[LeventK]

@Damian_Turcios
This expression will work provided you don’t need to count the start date and end date if their weekday is also Saturday and/or Sunday.

IFS(
	[Include?]=FALSE,
	IFS(
		OR(WEEKDAY([StartDate])=7,WEEKDAY([StartDate])=1),
		IFS(
			YEAR([EndDate]) > YEAR([StartDate]), (52 - WEEKNUM([StartDate]) + WEEKNUM([EndDate])) * 2 - 2,
			YEAR([EndDate]) = YEAR([StartDate]), (WEEKNUM([EndDate]) - WEEKNUM([StartDate])) * 2 - 2
		),
		TRUE,
		IFS(
			YEAR([EndDate]) > YEAR([StartDate]), (52 - WEEKNUM([StartDate]) + WEEKNUM([EndDate])) * 2,
			YEAR([EndDate]) = YEAR([StartDate]), (WEEKNUM([EndDate]) - WEEKNUM([StartDate])) * 2
		)
	),
	TRUE,
	IFS(
		YEAR([EndDate]) > YEAR([StartDate]), (52 - WEEKNUM([StartDate]) + WEEKNUM([EndDate])) * 2,
		YEAR([EndDate]) = YEAR([StartDate]), (WEEKNUM([EndDate]) - WEEKNUM([StartDate])) * 2
	)
)

I have tested this and it works. You can test it in my public app below. Just click on your name in the deck view and create a record. When you enter a start and end date, the Virtual Column will calculate the total number of Satudays and Sundays in between those dates. I have also added an option to include or exclude the start and end date from the calculation if their weekdays are Saturday and/or Sunday.

[Damian_Turcios]

THANK YOU LeventK, I REALLY APPRECIATE IT.

[LeventK]

You’re welcome @Damian_Turcios, my pleasure to be helped of.

[OptimiX_XcrY]

I tested in this pressented app 14 nov 2020 to 20 nov 2020, with NO checked. It results zero days, but it should be 1 (15 nov 2020 is a sunday). 14 to 21 with yes is results 2 , but it should be 3 (14,15 and 21 niv 2020)

Did the functions involved changed functionality in the meantime, or am I missing something ?

[OptimiX_XcrY]

I rebuild the formula for my simpler purpose (same year for start and end date , counting start and end day too) :

2*(weeknum([DataConcFinal])-weeknum([DataConcStart])+1) -
(ifs(weekday([DataConcStart])<>1;1)+
ifs(weekday([DataConcFinal])<>7;1))