# Help with workday formula

(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 ?

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