Number of working days in a month

AppSheet AppSheet Q&A
1

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

2

Here’s a post from @Steve that you should find helpful:

[Count A certain day in a Mounth](https://community.appsheet.com/t/count-a-certain-day-in-a-mounth/13036/4) Questions

You can try this expression to identify how many times a given weekday (1-7 for Sun-Sat) occurs in a month containing a given date: ceiling( day( eomonth([date], 0) - weekday(eomonth([date], 0)) - ifs(([weekday] > weekday(eomonth([date], 0))), 7) + [weekday] ) / 7.0 ) eomonth([date], 0) gets the date of the last day of the month that contains [date]. weekday(eomonth([date], 0)) get the number of the weekday of that last day of the month. eomonth(…) - weekday(……

1 Like
3

Arnab_Dutta:

Is there a built-in formula to do so?

There is not.

4

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.

1 Like
5

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.

6

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)

2 Likes
7

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
)

1 Like