How to Count Weekdays from the Last Date to Today in Excel
Question details
The user needs to count the number of working days (excluding weekends) from the last recorded date in a column up to the current date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking elapsed working days since the most recent entry in a spreadsheet column without manually updating the start date.
- Observed behavior
- Needs a dynamic formula that automatically identifies the last non-blank date in a specific column and calculates the weekdays up to today.
Ensure that the column containing your dates is formatted correctly as Dates, and that there are no hidden text strings or spaces in what appear to be blank cells.
Use the NETWORKDAYS and LOOKUP Functions
Combine NETWORKDAYS to count working days with a LOOKUP function to dynamically find the last non-blank date in a column.
The NETWORKDAYS function automatically excludes Saturdays and Sundays when calculating the difference between two dates.
By nesting a LOOKUP function, Excel can scan a column (like Column A) from bottom to top to find the last cell that isn't empty, ensuring your formula always uses the most recently entered date.
Click on the cell where you want the calculated number of weekdays to appear.
Type the formula =NETWORKDAYS(LOOKUP(2,1/(A:A<>""),A:A),TODAY()) into the formula bar. Replace 'A:A' with your actual column letter if your dates are in a different column.
Press the Enter key to apply the formula. The cell will now display the number of working days from the last entered date to today.
If you want to exclude specific public holidays, create a list of holiday dates in another range (e.g., C1:C10) and add it as the third argument: =NETWORKDAYS(LOOKUP(2,1/(A:A<>""),A:A),TODAY(), C1:C10).
Calculate Working Days Easily in WPS Spreadsheet
WPS Office offers a powerful, free alternative to Microsoft Excel. WPS Spreadsheet fully supports the NETWORKDAYS and LOOKUP functions, allowing you to manage dates, track schedules, and calculate working days seamlessly.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
- 2. Select a cell: Click the cell where you want to display the weekday count.
- 3. Apply the formula: Paste =NETWORKDAYS(LOOKUP(2,1/(A:A<>""),A:A),TODAY()) and press Enter.
Frequently Asked Questions
Can I include custom holidays in the NETWORKDAYS formula?
Yes. You can add a range of cells containing your holiday dates as the third argument in the formula, like =NETWORKDAYS(Start_Date, End_Date, Holiday_Range).
Why does the LOOKUP function use '2' and '1/'?
The expression 1/(A:A<>"") creates an array of 1s (for non-blank cells) and errors (for blank cells). Searching for 2 forces LOOKUP to find the last valid numeric value (the 1), effectively returning the last non-blank entry in the column.
Does this formula work if there are blank rows between dates?
Yes, the LOOKUP method specifically targets the last non-blank cell in the entire column, regardless of any empty cells that appear before it.
How can I count working days if my weekend is not Saturday and Sunday?
If your weekends fall on different days, use the NETWORKDAYS.INTL function instead. It allows you to specify exactly which days of the week should be treated as weekends using custom weekend parameters.
