Excel: Determine if Date Has Passed (Easy Ways)

To determine if a date has passed in Excel, compare the date to today’s date using a formula like =A2<TODAY(), which returns TRUE for past dates and FALSE for today or future dates. You can also use the IF function to display custom text such as “Passed” or “Upcoming” instead of TRUE/FALSE. This guide explains the formulas, how to handle blank cells and future dates, and common pitfalls.

Formula

The core formula is =A2<TODAY() where A2 contains the date you are checking. TODAY() returns the current date (without time) and updates each time the worksheet recalculates. The comparison operator < asks: is the date in A2 earlier than today? If yes, the result is TRUE (date has passed); if no, the result is FALSE (today or future).

To display a friendly message, use =IF(A2<TODAY(),"Passed","Upcoming"). You can replace the text with anything you like, such as “Overdue” or “Pending”.

Worked Example

Suppose cell A2 contains the date 2024-05-15 and today is 2025-04-10. The formula =A2<TODAY() evaluates to 2024-05-15 < 2025-04-10, which is TRUE. So the result is TRUE, meaning the date has passed. If A2 contained 2025-04-10 (today), the result would be FALSE because the date is not strictly less than today. If A2 contained 2025-12-31, the result would also be FALSE.

How to Use the Formula

• Click the cell where you want the result (e.g., B2).
• Type =A2<TODAY() (replace A2 with your date cell).
• Press Enter. The cell shows TRUE or FALSE.
• To show custom text, use =IF(A2<TODAY(),"Passed","Upcoming").
• Copy the formula down the column for other dates.

Handling Blank Cells and Future Dates

If a date cell is empty, =A2<TODAY() returns FALSE because an empty cell is treated as 0 (which is less than today’s serial number). That might be misleading. To treat blanks as “not passed”, use =IF(A2="","No date",IF(A2<TODAY(),"Passed","Upcoming")). Similarly, if you want to include today as “passed” (i.e., deadline has arrived), use <= instead of <.

Using Conditional Formatting to Highlight Passed Dates

Conditional formatting can visually flag overdue dates. Select the date range, go to Home → Conditional Formatting → New Rule → Use a formula. Enter =A2<TODAY() (adjust the first cell reference). Set a format (e.g., red fill) and click OK. This will highlight all dates earlier than today. To highlight today as well, use <=.

Common Mistakes and Pitfalls

• Dates stored as text: If your dates are text, the comparison may fail. Convert them using DATEVALUE or the Text to Columns wizard.
• Time component: If a cell contains a date and time (e.g., 2025-04-10 14:00), TODAY() is midnight. So a date with a time later today will still be considered “not passed” because the serial number is greater than today’s midnight. Use INT(A2)<TODAY() to ignore the time.
• Volatile TODAY(): The function recalculates whenever the workbook opens or changes. If you need a static result, replace it with a fixed date or use VBA to paste values.

Alternatives: DATEDIF and NETWORKDAYS

If you need the number of days past due, use =DATEDIF(A2,TODAY(),"d") which returns the difference in days. For overdue days (negative if not passed), use =TODAY()-A2. To count only working days, use =NETWORKDAYS(A2,TODAY()). These are useful for aging reports or task management.

Frequently Asked Questions

How do I check if a date is more than 30 days past?

Use =IF(A2<TODAY()-30,"Over 30 days","Not over 30 days"). The expression TODAY()-30 gives the date 30 days ago. If your date is earlier than that, it has passed more than 30 days ago.

Why does my formula return FALSE for a date that is clearly in the past?

Check if the cell contains a date stored as text. Use =ISNUMBER(A2) to test. If FALSE, convert the text to a date using DATEVALUE(A2) or the Text to Columns feature. Also ensure the cell is formatted as a date, not as text.

Can I use this formula in Google Sheets?

Yes, the same formulas work in Google Sheets. The function TODAY() is identical, and the comparison operators behave the same.

How do I make the result update automatically every day?

TODAY() is volatile and recalculates automatically when the workbook opens or when any change is made. To force a recalculation, press F9. If you want a permanent stamp, copy the cell and paste as values.

What if I want to check if a date is passed but ignore the year?

Use =DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))<TODAY() to compare the month and day of the date against today’s date in the current year. This is useful for birthdays or annual renewals.

Related

• OnePlus 13 Offline Find My Device When Switched Off
• Cloud Storage vs File Sync: Editing on Multiple Devices
• Delete All Steam Cloud Saves? What Actually Happens

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2