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

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. We’ll use 4 different approaches to check if the dates are before today’s date.

excel formula if date is less than today


Method 1 – Using an Excel Formula to Determine If a Date Is Less Than Today

Steps:

  • Create a new column in Column E. This new column sits just beside the Submission Date column.
  • Give it a name. In this case, we named it Less than today.

Using Excel Formula to Determine If Date Is Less than Today

  • Select cell E5.
  • Insert 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. We’re checking whether the date in cell D5 is lower than the date of today or not.

  • Press Enter.

  • Bring the cursor to the bottom right corner of cell E5. It’ll look like a plus (+) sign. This the Fill Handle tool.
  • Double-click on it.

  • This copies the formula to the lower cells.

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


Method 2 – Utilizing the IF Function to Determine If a Date Is Less Than Today in Excel

Steps:

  • Select cell E5.
  • 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.

  • Press Enter.

Utilizing IF Function to Determine If Date Is Less than Today


Method 3 – Applying Conditional Formatting

Steps:

  • Select cells in the D5:D14 range.
  • Go to the Home tab.
  • Click on the Conditional Formatting drop-down on the Styles group.
  • Select New Rule from the list.

Applying Conditional Formatting

  • The New Formatting Rule dialog box opens.
  • Choose Use a formula to determine which cells to format under the Select a Rule Type section.
  • Put =D5<TODAY( ) in the Format values where this formula is true box.
  • Select the Format button.

  • The Format Cells wizard opens.
  • Move to the Fill tab.
  • Choose the Light Green color from the available options.
  • Click OK.

Applying Conditional Formatting

  • This returns to the New Formatting Rule dialog box.
  • Click OK again.

  • The dates lower than today get highlighted with our preferred color.

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


Method 4 – Using VBA Code to Determine If a Date Is Less Than Today

Steps:

  • Construct a new column named Status like before.

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

  • Go to the Developer tab.
  • Select Visual Basic on the Code group.
  • Alternatively, press Alt + F11.

  • The Microsoft Visual Basic for Applications window opens.
  • Go to the Insert tab and select Module from the options.

  • This opens the Code Module.
  • Copy 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

  • Select the Run icon or press F5 on the keyboard.

  • Return to the worksheet.
  • The Status column gets filled automatically with the correct result.

Employing VBA Code to Determine If Date Is Less than Today


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

Steps:

  • Select cells in the D5:D14 range.
  • Jump to the Home tab.
  • Click on the Conditional Formatting drop-down on the Styles group.
  • Select New Rule from the list.

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

  • The New Formatting Rule dialog box opens.
  • Choose the Format only cells that contain section.
  • Select between in the second box of Format only cells with.
  • Insert =TODAY( )-1 in the third box and =TODAY( )-30 in the fourth box.
  • Select the Format button.

  • Choose a Yellow Fill color in the Format Cells window and click OK.

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

  • Click OK again.

  • This highlights the cells containing dates lower 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 practice, we have provided a Practice section like below in each sheet on the right side.

Practice Section


Download the Practice Workbook


<< Go Back to Dates | Compare | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo