The NETWORKDAYS function in Excel allows you to return the number of workdays between two specified dates. This function automatically excludes weekends and gives you the option to include holidays as well. However, holiday dates must be defined. Lets begin by reviewing the syntax.
=NETWORKDAYS(start_date, end_date, [holidays])
- Start_date – Initial Date
- End_date – The end of the period to stop counting workdays.
- Holidays – (Optional) Omitted from being counted. Need to specify in separate list.
In our example below, we will use the NETWORKDAYS function to show how to calculate downtime charges for a late delivery. Work days are Monday-Friday.
Let’s begin by reviewing the syntax from the above example. We are trying to calculate the number of business days between the actual delivery date verse the original delivery date. In the first example, we are leaving the Holiday’s argument blank.
=NETWORKDAYS(A4,B4,)
As a result, Excel calculates the number of late business days to be 22.
In the next example, we are now adding in two holidays. Our holiday’s are Halloween and Thanksgiving. Yes, I’m using Halloween as a holiday. We define the holiday dates from cells B8-B9.
Please note, if the holiday date does not fall within the Start_Date and End_Date, excel will ignore the holiday. To clarify, if we added Christmas, 12/25/2019, excel would omit this date in our example.
=NETWORKDAYS(A4,B4,B8:B9)
This function is quite valuable for numerous spreadsheet applications. I have used this formula to calculate downtime charges based on vendor performance. The NETWORKDAYS function is one to keep in your Rolodex.
