Excel Formula to Find If Date Is Less Than Today (4 Examples)

Managing dates in Excel is a common task, especially when you need to track deadlines and project statuses. Sometimes, you need to quickly identify if a date has already passed relative to the current day. In this article, we will show you how to use various Excel features, including formulas, conditional formatting, and VBA, to check if a date is less than today’s date.

The simplest way to check if a date is less than today is by directly comparing the date using the TODAY function.

Suppose we have a list of books, the book borrower’s name, and their return date. We will determine if the book’s return date is less than today’s date.

Starting with, we will set up a new column to display a TRUE/FALSE result based on the comparison.

➤ Choose cell D2 and input the following formula.

➤ Drag the Fill Handle down from cell D2 to D11 to apply the formula to all the return dates.

As a result, column D will now show TRUE for dates that have passed and FALSE for dates that are today or in the future.

While the TODAY function provides a TRUE/FALSE output, often you might need a more descriptive status, like “Returned” or “Not returned.” This can be achieved by applying the comparison within an IF function.

➤ Select cell D2 and write down the following formula.

➤ Drag down the fill handle from cell D2 to D11 to apply this status formula to all entries.

As a result, column D will now clearly indicate “Returned” for books whose due dates have passed and “Not returned” for those whose dates are today or in the future.

Conditional formatting is a tool used for highlighting cells based on specific criteria. You can use it to automatically change the color of cells where the date is less than today, drawing immediate attention to overdue items.

➤ Select the range C2:C11 where you want to apply the formatting.
➤ Click on the Home tab from the menu bar.
➤ In the Styles group, click on Conditional Formatting.
➤ Choose New Rule from the dropdown menu.

➤ In the New Formatting Rule dialog box, select the option Use a formula to determine which cells to format.
➤ In the Format values where this formula is true field, enter the formula.

➤ Click the Format button to choose your desired formatting.

➤ In the Format Cells window, choose Fill, your desired color, and hit OK.

➤ Then click OK again in the New Formatting Rule dialog box.

As a result, any date in the selected range that is less than today’s date will automatically be highlighted with the formatting.

You can also apply VBA code to compare dates that are less than today. This is mostly used if you want to update the status without manually dragging formulas or if you have a very large dataset. Here, we will use VBA to write a simple macro that checks the return dates and updates the “Status” column based on whether the date is less than today.

➤ Click on the Developer tab from the menu bar.
➤ In the Code group, click on Visual Basic.

The Visual Basic for Applications window will open.

➤ From the menu bar, click on Insert.
➤ Choose Module.

➤ Copy and paste the following VBA code into the module and hit Run.

Sub CheckSubmissionStatus()
Dim rowNum As Integer
For rowNum = 2 To 11
If Cells(rowNum, 3).Value < Date Then
Cells(rowNum, 4).Value = "Returned"
Else
Cells(rowNum, 4).Value = "Not Returned"
End If
Next rowNum
End Sub

Finally, column D will be automatically updated with “Returned” or “Not Returned” statuses based on whether the return date is less than today.

Frequently Asked Questions

What if my date is stored as text and not recognized by Excel?

You can use the DATEVALUE function to convert the text into a proper date. For example, you can use =IF(DATEVALUE(A2) < TODAY(), “Returned”, “Not Returned”).

What happens if the cell is empty or contains text?

If the cell is empty or contains text that isn’t a valid date, the formula may return an error or unexpected results. To avoid this, you can use an additional check like: =IF(ISNUMBER(A2), IF(A2<TODAY(), “Returned”, “Not Returned”), “”).

How can I create a countdown to a future date?

You can use this formula: =A2-TODAY() that will return how many days are left. You can also use conditional formatting to highlight urgent deadlines.

Concluding Words

Above, we have explored various methods for checking if a date in Excel is less than today, using TODAY, IF functions, Conditional Formatting, and automated VBA code. You can utilize these techniques to manage and analyze date-related data within your Excel sheet. If you have any questions, feel free to leave them in the comments below.