How to Exclude Start and End Dates Using NETWORKDAYS Function
Question details
The user needs to calculate the number of workdays between two dates but wants to exclude the start and end dates from the final calculation.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating the strict working days between two specific dates without counting the starting and ending boundary dates.
- Observed behavior
- The default NETWORKDAYS function is inclusive, meaning it counts both the start and end dates. For example, calculating the days between Friday and Tuesday returns 3 days instead of 1 intermediate workday.
Ensure your start and end dates are formatted as valid dates in your spreadsheet, and verify which cells contain these dates before modifying the formula.
Use WEEKDAY to Subtract Both Start and End Dates
Modify the standard NETWORKDAYS formula to check if the start and end dates are weekdays, subtracting them individually from the total if they are.
By default, NETWORKDAYS is an inclusive function. To make it exclusive, you can evaluate the start and end dates using the WEEKDAY function. If a boundary date falls on a weekday (Monday to Friday), the formula evaluates to TRUE (which acts as 1 in math operations) and subtracts it from the total.
Click on the cell where you want the final exclusive workday count to be displayed.
Type the formula: =NETWORKDAYS(C1,D1)-(WEEKDAY(C1,2)<6)-(WEEKDAY(D1,2)<6). Replace C1 with your start date cell and D1 with your end date cell.
Press Enter. The result will now subtract 1 for the start date if it is a weekday, and subtract 1 for the end date if it is a weekday.
Use AND Function to Subtract Only If Both Are Weekdays
Use this alternative if your specific logic requires subtracting exactly 1 day only when both the start and end dates are weekdays simultaneously.
Easily Calculate Workdays with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time functions like NETWORKDAYS and WEEKDAY. It allows you to manage project timelines and calculate working days effortlessly while maintaining strict compatibility with traditional spreadsheet tools.
- 1. Open your document: Launch WPS Spreadsheet and open the file containing your project timelines or date ranges.
- 2. Select the formula cell: Click on an empty cell where you want to output the calculated working days.
- 3. Input the NETWORKDAYS function: Go to the Formulas tab, click Insert Function, search for NETWORKDAYS, or type the formula directly into the formula bar.
- 4. Get instant results: Press Enter to instantly process the calculation. You can drag the fill handle to apply the formula to other rows.
Frequently Asked Questions
Can I exclude public holidays in the NETWORKDAYS function?
Yes, NETWORKDAYS has an optional third argument for holidays. You can select a range of cells containing your specific holiday dates (e.g., =NETWORKDAYS(C1,D1, E1:E10)), and the function will automatically exclude them from the workday count.
What if my company's weekend is not Saturday and Sunday?
If your workweek has different rest days, you should use the NETWORKDAYS.INTL function instead. It allows you to specify exactly which days of the week should be considered weekends using a custom weekend string or predefined number.
Why does WEEKDAY(C1,2) use the number 2?
The '2' in the WEEKDAY function specifies the return type where Monday is represented as 1 and Sunday as 7. This makes it incredibly easy to check for standard weekdays by simply verifying if the return value is less than 6.
