Excel allows for calculations between dates, but as a default does not distinguish between weekdays, weekends and holidays, so subtracting one date from another will give the total number of days between them including weekends and holidays.
To calculate just the number of working days between two dates Excel has two functions, NETWORKDAYS, which assumes Saturday and Sunday are weekends, and NETWORKDAYS.INTL, which allows you to choose what days you want to treat as the weekend, both also give the option to add in holidays.
Download File
If you would like to follow along download the attachment below.
Traditional Date Calculations
When subtracting one date from another by default Excel does not include weekends or holidays.
The image below shows how Excel does this, correctly showing that May 31st is 30 days after May 1st.
While there are 30 days between these dates, during this time there were eight days that were weekends and in the UK there are two public holidays in May, none of which are included in the above calculation.
NETWORKDAYS Function
To be able to see how many working days there were during this time period based on a
Monday to Friday working week you can use NETWORKDAYS.
The NETWORKDAYS function has two compulsory criteria and one optional:
=NETWORKDAYS(start_date, end_date, [holidays])
start_date: The starting date of the period.
end_date: The ending date of the period.
holidays (optional): A list of non-working days (holidays) that should also be excluded from the count.
NETWORKDAYS without Holidays
When using the two compulsory criteria NETWORKDAYS defaults to only including weekdays based on a Monday-Friday working week in its calculation.
The above calculation counts all weekdays in the range between May 1st and May 31st, excluding Saturdays and Sundays but ignoring any days that may have been holidays.
NETWORKDAYS with Holidays
In the UK there are two public holidays in May, in 2024 these fell on the 6th and 27th, both of which are Mondays and would be treated as working days in the NETWORKDAYS calculation if nothing was entered in the optional holidays parameter.
To exclude these dates from the calculation, the dates of the holidays can be added to an appropriate area in your spreadsheet and then referenced in the holidays part of the NETWORKDAYS function.
NETWORKDAYS.INTL Function
The NETWORKDAYS.INTL function give you more control over what are weekdays or weekends, giving the option to change the working week from the standard Monday-Friday.
The syntax of the function is similar to NETWORKDAYS, but adds an additional optional parameter called weekend.
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
start_date: The starting date of the period.
end_date: The ending date of the period.
weekend (optional): A weekend pattern that defines which days of the week are considered non-working days. This can be either a number or a custom string.
holidays (optional): A list of non-working days (holidays) to exclude from the count.
Weekend Options Using Numbers
Using NETWORKDAYS.INTL allows you to choose what days you want to treat as a weekend by selecting a number from a preset list of weekend options.
The options available to treat as weekends are as follows:
Based on the number of working days with no public holidays using a Monday to Friday working week there are 23 working days in May 2024.
Using NETWORKDAYS.INTL it would be possible to change the weekends and return a different number of working days.
In the example below the number 6 has been entered in the weekend parameter, so the calculation has treated Thursday and Friday as weekends, and Saturday and Sunday are included in the total number of work days.
When the weekends are changed to this the function is now showing a total of 21 working days in May 2024.
It is possible to build this out even further and include holidays.
When the two UK public holidays that fall on a Monday are added to the holidays section the total working days in May 2024 when Thursday and Friday are treated as weekends is 19.
Weekend Options Using Custom String
For more flexibility you can use a 7-character string to define which days are considered weekends.
Each character represents a day of the week starting from Monday.
A "1" indicates that the day is a weekend, while a "0" means it is a working day.
This gives the option to treat non-consecutive days as weekends or include more or less than two days in the week as weekend.
Below are some examples of what strings you can create, although it is possible to create more than just these.
Excluding holidays when Saturday and Sunday are treated as weekends in May 24 there are 23 working days, and when Thursday and Friday are treated as weekends the number of working days in May 24 is 21.
Using the custom string option if the non-consecutive days of Tuesday and Thursday are treated as weekends by entering “0101000” in the weekend part of the NETWORKDAYS.INTL function there are a total of 22 working days in May 2024.
When using a string to define weekend days it is also possible to include holidays in the calculation.
Conclusion
While Excel allows calculations between dates as a default they do not exclude weekends or holidays.
Both NETWORKDAYS and NETWORKDAYS.INTL do exclude weekends with the option to exclude holidays as well.
NETWORKDAYS assumes a working week of Monday to Friday with Saturday and Sunday as weekends, while NETWORKDAYS.INTL gives an additional option to change the start of the work week or enter specific days as the weekend.
Comments