Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

Need to learn how to use Excel formula if a date is less than today? Excel provides us with great approaches to use date formatting depending on different conditions. Here, we will take you through 4 easy and convenient methods on how to use the Excel formula if a date is less than today.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


4 Methods to Use Excel Formula to Determine If Date Is Less Than Today

For ease of understanding, let’s say we have a Student-wise Submission Dates list of a certain institution. This dataset contains the First Names, Last Names, and Submission Dates in columns B, C, and D respectively.

excel formula if date is less than today

Now, we’ll use 4 different approaches to check if the dates are less than today’s date. Let’s go through them one by one.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Using Excel Formula to Determine If Date Is Less Than Today

In our first method, we’ll use a quite easy Excel formula to check if the date is less than today. So, follow these simple steps below.

📌 Steps:

  • At the very beginning, create a new column in Column E. This new column sits just beside the Submission Date column.
  • Then, give it a name. In this case, we named it Less than today. Because here we’ll check if its adjacent column’s value is less than today or not.

Using Excel Formula to Determine If Date Is Less than Today

  • At this moment, select cell E5.
  • After that, write down the following formula.
=D5<TODAY()

Here, D5 represents the cell reference of the Submission Date of the first student Harry Albert. And the TODAY function returns today’s date only. Here, we’re checking whether the date in cell D5 is less than the date of today or not.

  • Later, press ENTER.

  • Now, bring the cursor to the bottom right corner of cell E5. Thus, it’ll look like a plus (+) sign. It’s the Fill Handle tool.
  • Then, double-click on it.

  • Automatically, it copies the formula to the lower cells. Hence, we also get the results in the remaining cells also.

Using Fill Handle to Use Excel Formula If Date Is Less than Today

Read More: How to Compare Dates in Two Columns in Excel (8 Methods)


2. Utilizing IF Function to Determine If Date Is Less Than Today in Excel

As with Excel, there are multiple ways of doing the same task. Here, we’ll use the IF function to do the same job. In this approach, we’ll find out whether the assignment is submitted or not on today’s date. It’s simple & easy, just follow along.

📌 Steps:

  • At first, select cell E5.
  • Then, paste the following formula into the Formula Bar.
=IF(D5<TODAY(),"Submitted","Not Submitted")

Here, the IF function performs a logical test which is the value in cell D5 is less than today’s date. If the condition is true, then it’ll show Submitted in cell E5. Otherwise, it shows Not Submitted in the cell.

  • Lastly, press the ENTER key.

Utilizing IF Function to Determine If Date Is Less than Today

Read More: How to Compare Dates to Today with Excel VBA (3 Easy Ways)


Similar Readings


3. Applying Conditional Formatting

If you are curious to learn Conditional Formatting in Excel, this method may come in handy. Let’s explore the method step by step.

📌 Steps:

  • Firstly, select cells in the D5:D14 range.
  • Secondly, go to the Home tab.
  • Thirdly, click on the Conditional Formatting drop-down on the Styles group.
  • Fourthly, select New Rule from the list.

Applying Conditional Formatting

  • Instantly, the New Formatting Rule dialog box opens.
  • Here, choose Use a formula to determine which cells to format under the Select a Rule Type section.
  • Then, we’ve to make some edits in the Edit the Rule Description section.
  • Later, write down =D5<TODAY( ) in the Format values where this formula is true box.
  • After that, select the Format button.

  • As a result, the Format Cells wizard opens.
  • Then, move to the Fill tab.
  • Later, choose Light Green color from the available options.
  • Next, click OK.

Applying Conditional Formatting

  • Again, it returns us to the New Formatting Rule dialog box.
  • Here, click Ok again.

  • Thus, the dates less than today get highlighted with our preferred color.

Applying Conditional Formatting to Check If Date Is Less than Today in Excel

Read More: How to Compare If Date Is Before Another Date in Excel


4. Employing VBA Code to Determine If Date Is Less Than Today

Another alternative is to employ the VBA code. Although using formulas is a quick way of editing data, it can be difficult to interpret. Furthermore, if you often need to do the task in a different way, then you may consider the VBA code below. So let’s begin.

📌 Steps:

  • Primarily, construct a new column named Status like before.

Employing VBA Code to Determine If Date Is Less than Today in Excel

  • Secondarily, go to the Developer tab.
  • Then, select Visual Basic on the Code group.
  • Alternatively, press ALT + F11 to do the same task.

  • Instantly, the Microsoft Visual Basic for Applications window opens.
  • Afterward, move to the Insert tab.
  • Later, select Module from the options.

  • Immediately, it opens the Code Module.
  • Then, write down the following code in the Module.
Sub tst_today()
Dim x As Integer
For x = 5 To 14
If Cells(x, 4).Value < Date Then
Cells(x, 5).Value = "Submitted"
Else
Cells(x, 5).Value = "Not Submitted"
End If
Next x
End Sub

Employing VBA Code to Determine If Date Is Less than Today

  • Lastly, select the Run icon or press F5 on the keyboard.

  • Now, return to the VBA worksheet.
  • Thus, the Status column gets filled automatically with the correct result.

Employing VBA Code to Determine If Date Is Less than Today

Read More: Conditional Formatting for Dates Older Than Certain Date in Excel


How to Check Whether the Date Is Less than Today and within 30 Days

If you want to learn to check whether the date is less than today and within 30 days, then this section may come in handy. So, without further delay, let’s see how we do it. Just track the following steps carefully.

📌 Steps:

  • First of all, select cells in the D5:D14 range.
  • Secondly, jump to the Home tab.
  • Then, click on the Conditional Formatting drop-down on the Styles group.
  • After that, select New Rule from the list.

How to Check Whether the Date Is Less than Today and within 30 Days

  • Immediately, the New Formatting Rule dialog box opens.
  • Here, choose Format only cells that contain under the Select a Rule Type section.
  • Then, we’ve to make some edits in the Edit the Rule Description section.
  • Hence, select between in the 2nd box of Format only cells with.
  • Later, write down =TODAY( )-1 in the 3rd box and =TODAY( )-30 in the 4th box.
  • After that, select the Format button.

  • Suddenly, the Format Cells wizard opens.
  • Then, proceed to the Fill tab.
  • Later, choose Yellow color from the available options.
  • Next, click OK.

Applying Conditional Formatting to Use Excel Formula If Date Is Less than Today

  • Similarly, it returns us to the New Formatting Rule dialog box.
  • Then, click Ok again.

  • Thus, it highlights the cells containing dates less than today’s date and within 30 days from today.

Applying Conditional Formatting to Use Excel Formula If Date Is Less than Today


Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

Practice Section


Conclusion

This article provides easy and brief solutions to use Excel formulas to determine if the date is less than today. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo