How to Calculate Weekdays Between Two Dates Excluding Start Date
Question details
The user needs to calculate the number of working days between a creation date and an end date without counting the start date, weekends, or specific listed holidays, ensuring the formula returns zero if no valid working days exist in the interval.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking project durations, SLA compliance, or processing times where the initial creation date should not be counted as a working day.
- Observed behavior
- A formula is required to count only valid weekdays strictly after the start date, accurately excluding weekends and a custom holiday list, while preventing negative day counts.
Ensure that your start and end dates are formatted as valid dates in your spreadsheet, and prepare a designated cell range containing your custom holiday dates.
Use NETWORKDAYS.INTL with MAX to Exclude Start Date
This formula combination dynamically counts working days excluding weekends and custom holidays, explicitly skips the start date by adding 1 to it, and prevents negative numbers using the MAX function.
The NETWORKDAYS.INTL function is highly flexible, allowing you to define which days are considered weekends. By adding 1 day to the start date reference, we force the function to begin counting from the day after creation. Wrapping it in a MAX function ensures that if the end date is earlier or the same as the start date, the result defaults to zero instead of a negative number.
Ensure your Start Date is in cell A2 and End Date is in cell B2.
List your custom holiday dates in a separate range, for example, L6:L34.
Select the cell where you want the duration result to appear and enter the formula `=MAX(0,NETWORKDAYS.INTL(A2+1,B2,1,$L$6:$L$34))`.
Press Enter to see the result. You can then drag the fill handle down to apply this formula to other rows in your dataset.
Calculate Working Days Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time functions, including NETWORKDAYS.INTL. It is an excellent tool for tracking project timelines, employee attendance, and business metrics without needing complex workarounds.
- 1. Open Your File: Launch WPS Spreadsheet and open the document containing your dates.
- 2. Select Target Cell: Click on the cell where the working day duration will be calculated.
- 3. Access Date Functions: Go to the 'Formulas' tab and select 'Date & Time' to find NETWORKDAYS.INTL, or type the formula directly into the formula bar.
- 4. Calculate Result: Select your start date, end date, and holiday range, then press Enter to instantly get your working day count.
Frequently Asked Questions
Why is my formula returning a negative number instead of zero?
If your end date is earlier than your start date, the NETWORKDAYS.INTL function will natively return a negative value. Wrapping your formula inside `=MAX(0, ...)` forces the spreadsheet to evaluate the result and display zero if the count drops below zero.
How do I change the weekend days if my company only works Monday to Friday?
In the NETWORKDAYS.INTL formula, the third parameter determines the weekend. Using '1' specifies Saturday and Sunday as the weekend. If your schedule differs, you can change this parameter (for example, '11' sets Sunday only as the weekend).
What happens if I don't lock the holiday range with dollar signs ($)?
If you copy the formula down to other rows without locking the holiday range (using $L$6:$L$34 instead of L6:L34), the referenced holiday range will shift downwards row by row. This will cause the formula to exclude valid holidays and return an incorrect working day count.
