How to calculate the number of work days for a four-day workweek
This tip is how to calculate the number of work days for less than five-day workweek.
For example, you may need to find out how many business days should be in November 2018. This calculation should exclude Saturdays, Sundays, Mondays, and holidays:
![Calculate the number of work days for a four-day workweek in Excel 2016 Calculate the number of work days for a four-day workweek in Excel 2016](/images/tips/519/1.png)
1. To count days between two dates, excluding Saturdays, Sundays and Mondays, use the formula:
= SUMPRODUCT (-- (WEEKDAY (<date range>) = <day of week>))
where:
- <date range> is an array generated from two dates, using ROW and
INDIRECT formulas:
date range: ROW (INDIRECT (<start date> &":"& <end date>)),
- <day of week> taken from the WEEKDAY formula, a number between 1 and 7 that corresponds to a particular day of the week. With default settings, 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday and 7 = Saturday.
= SUMPRODUCT ( | -- (WEEKDAY (ROW (INDIRECT (B11 &":"& C11))) <> E2), |
-- (WEEKDAY (ROW (INDIRECT (B11 &":"& C11))) <> E3), | |
-- (WEEKDAY (ROW (INDIRECT (B11 &":"& C11))) <> E8)) |
![Long formula SUMPRODUCT in Excel 2016 Long formula SUMPRODUCT in Excel 2016](/images/tips/519/2.png)
- the formula (WEEKDAY (<date range>) = 1) returns the array of TRUE and FALSE,
- (-- <array>) returns array of 1 and 0 instead of TRUE and FALSE,
- SUMPRODUCT sums all values of an array.
See more about this formula in How to count days of week between two dates.
It is easier to use ISNA and MATCH formulas instead of adding conditions three times:
= SUMPRODUCT (-- (ISNA (MATCH (WEEKDAY (<date range>), <array of week days>, 0))))
- MATCH returns the relative position of an item in an array that matches a specified value in a specified order,
- ISNA checks whether a value is #N/A, and returns TRUE or FALSE:
![SUMPRODUCT with ISNA and MATCH in Excel 2016 SUMPRODUCT with ISNA and MATCH in Excel 2016](/images/tips/519/3.png)
So,
- the formula MATCH (WEEKDAY (<date range>), <array of weekdays>, 0) returns the array of positions for week days (Sunday, Monday, and Saturday) and the value #N/A for all other days,
- ISNA () returns an array of TRUE and FALSE for values not #N/A and #N/A,
- (-- <array>) returns array of 1 and 0 instead of TRUE and FALSE,
- SUMPRODUCT sums all values of an array.
2. After calculating the number of working days, it is necessary to exclude all holidays:
The formula is (see How to count days of week in the range of dates):
= SUMPRODUCT (-- (WEEKDAY (<holidays>) <> <day of week>))
or
= SUMPRODUCT (-- (WEEKDAY (B2:B5) <> E2), -- (WEEKDAY (B2:B5) <> E3), -- (WEEKDAY (B2:B5) <> E4)):
![Long Formula to calculate the number of work days for a four-day workweek in Excel 2016 Long Formula to calculate the number of work days for a four-day workweek in Excel 2016](/images/tips/519/4.png)
With ISNA and MATCH, this formula will look shorter:
![Formula to calculate the number of work days for a four-day workweek in Excel 2016 Formula to calculate the number of work days for a four-day workweek in Excel 2016](/images/tips/519/5.png)
See also this tip in French: Comment calculer le nombre de jours de travail pour une semaine de travail de quatre jours.