Get Work Days with NETWORKDAYS and WORKDAY

Do you need to find out how many working days –excluding weekends and holidays– there are in a given period?  Or find out on what date a given number of work days will end?  You need NETWORKDAYS and WORKDAY.

The NETWORKDAYS function in Microsoft Excel returns the number of whole working days inclusive from a start date to an end date. Working days exclude weekends and any dates listed as holidays. Use NETWORKDAYS to calculate pay or holiday benefits based on the number of days worked during a specific period.

The WORKDAY function in Excel complements NETWORKDAYS.  It calculates the date that is a given number of working days after or before the start date. As with NETWORKDAYS, working days exclude weekends and any dates listed as holidays.

Note: NETWORKDAYS and WORKDAY treat weekends as the whole day Saturday and Sunday.  If your weekends are different, use the international versions, NETWORKDAYS.INTL and WORKDAY.INTL (below).  They let you specify your own weekends.

Syntax of NETWORKDAYS and WORKDAY

NETWORKDAYS(start_date, end_date, [holidays])

WORKDAY(start_date, days, [holidays])

Their arguments (almost identical) are:

• Start_date and end_date (required): The start and end date dates for the calculation. The start_date can be earlier than, the same as, or later than the end_date.  The latter will produce a negative answer.
• Days (required): The integer number of working days after start_date.  It excludes weekends and holidays. Use a negative value to get an earlier date.
• Holidays (optional): An optional range of dates to exclude from the working days.

Important:  Make sure that all dates are numeric date serial numbers. You can enter them with the DATE function or other date functions, or type them.  Or they can be the result of date calculations. However, if you enter them in a format that your Excel does not recognise as a date, it will see them as text, with a value of zero. If you format the cell as Number, you should have a number in the 44-thousands or higher.  That is the number of days since 31 December 1899.

Examples

Microsoft Excel stores dates as integers, starting at 1 January 1900.  Thus, you can deduct one date from another to get the number of days between the two dates.

The DAYS function behaves exactly the same as subtracting dates.

To take find working days, excluding weekends and holidays, you need the NETWORKDAYS function.  Be aware that this function counts both the start and end days, unlike DAYS or subtraction.  Thus, NETWORKDAYS(Day_1, Day_1) = 1, whereas DAYS(Day_1, Day_1) = 0.

Just to be inconsistent, WORKDAY does not count start_date. Thus, as we see above in row 11, adding 10 working days to a date gives a date exactly two weeks later (if there are no holidays).  Whereas using NETWORKDAYS on those same two dates gives 11 working days (row 12).

NETWORKDAYS.INTL and WORKDAY.INTL

If your weekends are not Saturday and Sunday, use these international equivalents of the functions above. These let you specify your own weekends.

These functions have the following syntax:

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

WORKDAY.INTL(start_date, days, [weekend], [holidays])

All parameters are the same as for NETWORKDAYS and WORKDAY above except for the optional weekend.  It can be a number or a string.  The number version is complex.  It is best to refer to online help for NETWORKDAYS.INTL and WORKDAY.INTL.

As a string, the weekend parameter is simpler.  It must consist of exactly 7 zeros or ones, representing the days of the week from Monday to Sunday.  “0” represents a working day and “1” a weekend (non-working) day.  Thus, the string “0000110” denotes a weekend of Friday and Saturday. This is the case in Israel and various Arab countries including Algeria, Egypt, Iraq, Jordan, Libya, Qatar, Saudi Arabia and the UAE.