How to Count Workdays with NETWORKDAYS and ISBLANK in Excel
Question details
The user needs a formula to calculate the elapsed weekdays between a submission date and an approval date, dynamically using the current date if the approval date is empty.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating elapsed workdays for project tracking or approval workflows where some tasks are still pending and lack an end date.
- Observed behavior
- The formula successfully counts the working days up to today for pending items, and between the specified dates for completed items, with an option to exclude the final day.
Ensure that your start and end dates are properly formatted as 'Date' in Excel rather than 'Text' to prevent formula calculation errors.
Use NETWORKDAYS and ISBLANK to calculate elapsed workdays
This solution uses a combined formula to check if the end date is empty, dynamically substituting the current date and calculating the elapsed weekdays.
The NETWORKDAYS function automatically excludes weekends. By default, it counts both the start date and the end date. If you want to measure just the elapsed time (for example, counting yesterday to today as 1 day instead of 2), you need to subtract 1 from the result.
Click on the cell where you want the calculated workdays to appear.
Type the formula: =IF(ISBLANK(G36),NETWORKDAYS(F36,TODAY()),NETWORKDAYS(F36,G36)-1) and press Enter. Replace F36 with your submission date cell and G36 with your approval date cell.
Click the small square at the bottom-right corner of the selected cell and drag it down to apply the formula to the rest of your data.
Calculate workdays including the end date
Use this variation if your tracking requirements dictate that both the submission day and the approval day must be counted as full working days.
Easily Calculate Workdays with WPS Spreadsheet
WPS Spreadsheet fully supports Excel's advanced date and time functions, including NETWORKDAYS and ISBLANK. You can seamlessly calculate project timelines and track approvals with the exact same formulas in a fast, lightweight application.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your submission and approval dates.
- 2. Select the calculation cell: Click on the empty cell where you want to calculate the elapsed time.
- 3. Input the combined formula: Type =IF(ISBLANK(G36),NETWORKDAYS(F36,TODAY()),NETWORKDAYS(F36,G36)-1) and press Enter.
- 4. Fill down the column: Drag the fill handle downwards to quickly calculate the workdays for all your remaining rows.
Frequently Asked Questions
How do I exclude specific holidays using this formula?
You can replace NETWORKDAYS with NETWORKDAYS.INTL. Set up a list of holiday dates in your sheet, then add that range to your formula. For example: =IF(ISBLANK(G36),NETWORKDAYS.INTL(F36,TODAY(),1,Holidays!A2:A10),NETWORKDAYS.INTL(F36,G36,1,Holidays!A2:A10)-1).
Why am I getting a negative number as a result?
A negative number usually occurs if your start date (submission date) is later than your end date (approval date or today's date). Verify that the column representing the start date actually contains the earlier dates.
Why does the formula return a #VALUE! error?
The #VALUE! error happens when the referenced cells contain text that cannot be recognized as valid dates. Check that both date columns are formatted properly as Dates and remove any hidden text characters or spaces.
