Number of Working Days this year with Wednesday Holiday

[trpatel123]

Hi All,

Our company is working 6 days a week with Wednesday is a non working day.

I want working days by month for this year...

Date1	Date2	Total         Days	Total Working       Days
01/01/2016	01/02/2016	31	27
01/02/2016	01/03/2016	29	25
01/03/2016	01/04/2016	31	26
01/04/2016	01/05/2016	30	26
01/05/2016	01/06/2016	31	27
01/06/2016	01/07/2016	30	25
01/07/2016	01/08/2016	31	27
01/08/2016	01/09/2016	31	26
01/09/2016	01/10/2016	30	26
01/10/2016	01/11/2016	31	27
01/11/2016	01/12/2016	30	25
01/12/2016	01/01/2017	31	27

I have Date1 and Date2 available... need to have formula for Total Working Days excluding Wednesday.

This should be at front end as I don't have access for script change and also sometimes I want to select two dates and need working days excluding number of Wednesday between those two dates.

[marcus_sommer]

If you used a master-calendar you could on a month-level calculate something like this:

count({< Date = {"=match(weekday(Date), 0,1,2,4,5,6)"}>} Date)

Easier is of course to flag the working-days within the calendar, see: Master Calendar with movable holidays‌ (you would need to switch the excluding day from sunday to wednesday).

- Marcus

[Gysbert_Wassenaar · Accepted solution]

Try: =Date2 - Date1 - Div(Date2-Date1,7) - (WeekDay(Date1)<=2)*(WeekDay(Date2)>=2)

[trpatel123]

Thanks Gysbert,

This is working absolutely fine !

Can you explain the formula you have written?

[trpatel123]

gwassenaar‌ can you please explain the logic here?

[Gysbert_Wassenaar]

• Date2 - Date1 is the number of days between the two dates
• Div(Date2 - Date1,7) is the number of whole weeks between the two dates. Every whole week will have one wednesday.
• (WeekDay(Date1)<=2) is test that checks if the weekday number of Date1 is smaller or equal to the weekday number of wednesday. The test returns 0 (false) or -1 (true).
• (WeekDay(Date2)>=2) is test that checks if the weekday number of Date2 is larger or equal to the weekday number of wednesday. The test returns 0 (false) or -1 (true).

The two tests are necessary to find the additional wednesday in a period that's not exactly N weeks long.

The best way to understand the expression is to create a table with start and end dates and a column for each separate component.