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:

Calculate the number of work days for a four-day workweek in Excel 2016

    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
  • 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.

See more about this formula in How to count day 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

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):

Long Formula to calculate the number of work days for a four-day workweek in Excel 2016

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

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.