How to Count Workdays Until Today or an End Date in Excel
Question details
The user needs an Excel formula to count the number of workdays between a start date and either today's date or a specific end date (whichever comes first), excluding weekends and holidays, and leaving the cell blank if dates are missing.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project progress, employee attendance, or task timelines where the workday count must dynamically stop at the current date or cap at a final deadline without returning errors on empty rows.
- Observed behavior
- The desired formula should evaluate multiple conditions: ignoring weekends/holidays, stopping the count at the end date or today, and returning a blank string when start or end date cells are empty.
Ensure that your start dates, end dates, and holiday dates are properly formatted as Date values in your spreadsheet, rather than as text strings, so the formulas can recognize them correctly.
Use the NETWORKDAYS Formula Combined with MIN and TODAY
This solution uses a nested formula to dynamically calculate the workdays, capping the end boundary at today's date if the project end date hasn't been reached yet.
The MIN function compares today's date and your specified end date, returning whichever is earlier. The NETWORKDAYS function then counts the working days between your start date and that earlier date, automatically excluding weekends and any holidays you provide.
The IF and OR functions at the beginning of the formula ensure that if either the start date or end date is missing, the cell remains blank instead of displaying a calculation error.
Ensure your Start Date is in column A, End Date is in column B, and create a separate range for your holiday dates (for example, F2:F100).
Click on the cell where you want the number of days to appear (e.g., C2).
Type the formula: =IF(OR(A2="",B2=""),"",NETWORKDAYS(A2,MIN(TODAY(),B2),$F$2:$F$100)) and press Enter.
If you do not need to exclude public holidays from your count, use this simplified formula instead: =IF(OR(A2="",B2=""),"",NETWORKDAYS(A2,MIN(TODAY(),B2)))
Click the small square at the bottom right corner of the target cell and drag the fill handle down to apply this formula to the rest of your data rows.
Calculate Complex Dates Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced Date & Time functions like NETWORKDAYS, TODAY, and MIN. You can seamlessly calculate project timelines and working days using the exact same formulas as Microsoft Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your project dates.
- 2. Enter the formula: Click on the cell for your workday count and type the =NETWORKDAYS() combined formula.
- 3. Select your parameters: Reference your Start Date, use MIN(TODAY(), End_Date), and highlight your holiday range.
- 4. Calculate instantly: Press Enter to instantly calculate the valid working days, then drag the fill handle to apply it across your sheet.
Frequently Asked Questions
Why does my NETWORKDAYS formula return a #VALUE! error?
This usually happens if the start dates, end dates, or holiday dates are formatted as text instead of valid dates. Ensure your cells are correctly formatted by right-clicking them, selecting 'Format Cells', and choosing 'Date'.
Can I include specific weekend days as workdays in my calculation?
Yes, you can use the NETWORKDAYS.INTL function instead of NETWORKDAYS. It includes a specific parameter that allows you to customize exactly which days of the week should be considered weekends or non-working days.
What if I only want to count workdays up to an end date without considering today's date?
You can simplify the formula by removing the MIN and TODAY functions. Simply use =NETWORKDAYS(A2, B2, $F$2:$F$100). This will calculate the total working days strictly between the start and end dates.
