How to calculate the number of work days for a four-day workweek

Excel 2016
When calculating the difference between two dates, you may want to exclude weekends and holidays. Excel proposes standard formula for calculating the number of work days for standard, five-day workweek. Also, Excel proposes formula for calculating the number of work days for different five-day workweek or six-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: 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) • 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 array.

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: So,

• the formula MATCH (WEEKDAY (<date range>), <array of week days>, 0) returns the array of positions for week days (Sunday, Monday and Saturday) and the value #N/A for all other days,
• ISNA() returns 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 array.

2.   After calculating the number of working days, it is necessary to exclude all holidays:

The formula is (see How to count day 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): With ISNA and MATCH, this formula will look shorter: If you have any questions or suggestions, please feel free to ask OfficeToolTips team.