'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Check if Today is Past a Due Date in Excel (Formula)
Question details
The user needs an Excel formula to compare the current date with a specific due date to determine if it is past due, while avoiding common syntax errors like #NAME?.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking deadlines or project due dates in a spreadsheet.
- Observed behavior
- Comparing a cell date to the current date using the TODAY() function without returning a #NAME? error or encountering text string issues.
Ensure that your due dates (e.g., in cell K3) are formatted as valid Excel dates, not as plain text, so the TODAY() function can accurately compare them.
Use the IF and TODAY() Functions to Check Due Dates
The most effective way to determine if a date is past due is by combining the IF function with the TODAY() function.
The TODAY() function automatically retrieves the current system date. Because it is a function, it strictly requires empty parentheses (). Without them, Excel attempts to interpret 'TODAY' as a named range, returning a #NAME? error.
By nesting this within an IF statement, you can instruct Excel to output specific text, such as 'PASSED DUE' or 'OK', based on whether the current date is mathematically greater than the target deadline.
Click on the blank cell where you want the status text (e.g., 'PASSED DUE' or 'OK') to appear.
Type the formula =IF(TODAY()>K3, "PASSED DUE", "OK") into the formula bar and press Enter. Make sure to replace 'K3' with the actual cell reference that contains your due date.
Hover over the bottom-right corner of the cell you just edited until the fill handle (a small plus icon) appears, then drag it down to apply the formula to the rest of your due date list.
Easily Track Due Dates in WPS Spreadsheet
WPS Office provides a highly capable Spreadsheet application where you can use advanced formulas like IF and TODAY() exactly as you would in Microsoft Excel to manage your deadlines effectively.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your deadline tracking workbook.
- 2. Insert the TODAY function: Click the cell next to your due date and input the formula =IF(TODAY()>K3, "PASSED DUE", "OK").
- 3. Format your dates: If needed, right-click the due date column, select 'Format Cells', and apply the correct Date format to ensure precise calculations.
Frequently Asked Questions
Why is my formula returning a #NAME? error instead of the date status?
This usually happens if you type TODAY without the parentheses. Excel recognizes it as an undefined text string or named range. Ensure you write it exactly as TODAY() for the formula to work properly.
Can I highlight the past-due cells instead of just showing text?
Yes. You can use Conditional Formatting for a visual warning. Select your due date cells, go to 'Conditional Formatting' > 'Highlight Cells Rules' > 'Less Than', and enter =TODAY() to format past dates in red.
How do I calculate the exact number of days a task is past due?
You can simply subtract the due date from today's date. Use the formula =TODAY()-K3 and ensure you format the result cell as 'Number' or 'General' rather than a Date to see the precise difference in days.
Does the TODAY() function update automatically if I leave the file open for days?
Yes, the TODAY() function is volatile. It will automatically refresh to the current date based on your computer's system clock whenever the workbook is opened, saved, or manually recalculated (by pressing F9).
