Help with workday formula

other
(Damian Turcios) #1

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

(Levent Kulacoglu) #2

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

(Levent Kulacoglu) #3

@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.

2 Likes
(Damian Turcios) #4

THANK YOU LeventK, I REALLY APPRECIATE IT.

(Levent Kulacoglu) #5

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