If you are managing a massive construction project or tracking employee leave in Microsoft Excel, calculating the duration between two dates is rarely as simple as subtracting the start date from the end date. A standard subtraction formula calculates every single day, including Saturdays and Sundays. If an employee takes two calendar weeks off, they did not consume 14 vacation days; they only consumed 10 working days. To accurately calculate durations while automatically stripping out weekends and custom holidays, you must use the NETWORKDAYS function.
How the NETWORKDAYS Function Works
The NETWORKDAYS function is a specialized date formula that instantly calculates the total number of Monday-through-Friday workdays existing between two specific calendar dates. It natively understands the Gregorian calendar and automatically ignores weekends without any manual configuration.
The syntax is: =NETWORKDAYS(start_date, end_date, [holidays])
- start_date: The cell containing the beginning date (e.g., A2).
- end_date: The cell containing the finishing date (e.g., B2).
- [holidays]: (Optional) A range of cells containing specific dates that should also be excluded from the final count (like national holidays).
How to Calculate Standard Working Days
Imagine cell A2 contains the project start date (10/1/2024) and cell B2 contains the deadline (10/31/2024).
Click into an empty cell (e.g., C2) and type:
=NETWORKDAYS(A2, B2)Excel will analyze the month of October 2024, identify that there are 8 weekend days (Saturdays and Sundays) buried within that specific month, subtract them from the total 31 calendar days, and accurately output 23 working days.
How to Exclude Custom Holidays
While Excel is smart enough to know when Saturday occurs, it does not know the specific dates of national bank holidays, nor does it know if your company unexpectedly closed for a random Tuesday due to a snowstorm. You must provide a custom holiday list.
- Create a small list of holiday dates in a separate column (e.g., type
10/14/2024into cell E2 for Columbus Day). - Update your formula to include that range:
=NETWORKDAYS(A2, B2, E2:E5)
Excel will recalculate the duration. It will strip out the 8 weekend days, and then it will check if any of the dates in your E2:E5 list fall within the project window. If they do, it subtracts them as well, lowering the final working day count to 22.
Note: If your company uses non-standard weekends (e.g., you work Tuesday through Saturday and take Sunday/Monday off), you must use the newer NETWORKDAYS.INTL function instead, which allows you to manually define which specific days of the week count as the weekend.