Hi,
I am trying to find out the number of working days in a month. Is there a built-in formula to do so?
Thanks,
Arnab
Hereโs a post from @Steve that you should find helpful:
There is not.
This would be easy if you add a table with one row for every date, but I really donโt like the idea of having to add a table just to get the result. Hopefully someone else can come up with a better solution using expressions only.
But if you want to try the table approach, just add a row for every date. Then add a column thatโs a Y/N, and test if each date is a WEEKDAY().
IFS(
WEEKDAY([THIS_ROWS_DATE])=1, FALSE,
WEEKDAY([THIS_ROWS_DATE])=7, FALSE,
TRUE, TRUE)
From there you should be able to COUNT(FILTER()) the rows that match the month/year and are weekdays.
Thanks all. I had implemented a solution using a another table but was looking for a formula similar to networkdays(). Iโll try to implement a formula using the existing data expressions and when successful Iโll post the soln here.
Not very elegant and i havenโt tested but it should be something likeโฆ
20+
SWITCH(WEEKDAY(EOMONTH(TODAY(),-1)+1),
1,IFS(DAY(EOMONTH(TODAY(),0))>28,DAY(EOMONTH(TODAY(),0))-29),
5,IFS(DAY(EOMONTH(TODAY(),0))<31,DAY(EOMONTH(TODAY(),0))-28),
6,IFS(DAY(EOMONTH(TODAY(),0))>28,1),
7,IFS(DAY(EOMONTH(TODAY(),0))>30,1),
DAY(EOMONTH(TODAY(),0))-28)
I have implemented this way. Created two virtual dates - StartDate and EndDate using EOMONTH formula. Then added this from https://community.appsheet.com/t/help-with-workday-formula/10775/3:
(HOUR([EndDate] - [StartDate])/24) - 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
)
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |