How to Return the Expected Value If the Date Is Greater Than Today in Excel

Example 1 – Set the Cell Value If the Date Is Greater Than Today

The dataset showcases products and their delivery date. Consider the delivery deadline is today: 8/28/2022.

Steps:

Select D5 and enter the following formula:

=IF(C5>=TODAY(),"Delayed","On time")

Press Enter.

In D5, the delivery status for the product mouse will display ‘On Time’.

Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Formula Breakdown

TODAY()

takes the date of the current day.

IF(C5>=TODAY(),”Delayed”,”On time”)

checks whether the value of C5 is greater than today. If the condition is met, the function will return “Delayed”. Otherwise, it returns “On Time”.

Example 2 – Delete Cell Value If the Date Is Greater Than Today

Steps:

Select D5 and enter the following formula:

=IF(C5>=TODAY(),"","On Time")

Press Enter.
In D5, the delivery status for the product mouse displays ‘On Time’.

Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Formula Breakdown

TODAY()

takes the date of the current day.

IF(C5>=TODAY(),””,”On Time”)

This formula checks whether the value in C5 is greater than today. If the condition is met, the function will return an empty cell. Otherwise, it returns “On Time”.

Using Excel VBA to Return the Expected Value If the Date Is Greater Than Today

1. Set Cell Value with VBA If Date Is Greater Than Today

You need to have the Developer tab on your ribbon. Click here to see how you can show the Developer tab on the ribbon.

Steps:

Open a VBA Window:

Go to the Developer tab.
Select Visual Basic in Code.

Insert a Module:

Go to the Insert tab.
Choose Module.

Enter the VBA Code:

Enter the following code.

Sub Set_1()
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]>=TODAY(),""Delayed"",""On Time"")"
    Range("D5").Select
    Selection.AutoFill Destination:=Range("D5:D10")
End Sub

Save the code.

Run the VBA Code:

Close the Visual Basic window.
Press Alt+F8.
In the Macro dialog box, select Set_1 in Macro name.
Click Run.

Output:

Code Breakdown

Sub Set_1()

names the sub-procedure.

 Range("D5").Select

selects D5.

 ActiveCell.FormulaR1C1 = "=IF(RC[-1]>=TODAY(),""Delayed"",""On Time"")"

The IF formula checks whether the value in C5 is greater than today. If the condition is met, the function will return “Delayed”. Otherwise, it returns “On Time”.

Range("D5").Select
   Selection.AutoFill Destination:=Range("D5:D10")

The code fills the specified cells with the formula.

End Sub

ends the sub-procedure.

2. Delete a Cell Value with VBA If the Date Is Greater Than Today

Steps:

Open the VBA Window:

Go to the Developer tab.
Select Visual Basic in Code.

Insert a Module:

Go to the Insert tab.
Choose Module.

Enter the VBA Code:

Enter the following code.

Sub Delete_1()
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]>=TODAY(),"" "",""On Time"")"
    Range("D5").Select
    Selection.AutoFill Destination:=Range("D5:D10")
End Sub

Save the code.

Run the VBA Code:

Close the Visual Basic window.
Press Alt+F8.
In the Macro dialog box, select Delete_1 in Macro name.
Click Run.

Output:

Code Breakdown

Sub Delete_1()

names the sub-procedure.

 Range("D5").Select

selects D5.

ActiveCell.FormulaR1C1 = "=IF(RC[-1]>=TODAY(),"" "",""On Time"")"

The IF formula checks whether the value in C5 is greater than today. If the condition is met, the function will return an empty cell. Otherwise, it returns “On Time”.

 Range("D5").Select
   Selection.AutoFill Destination:=Range("D5:D10")

The code fills the specified cells with the formula.

End Sub

ends the sub-procedure.

3. Modify a Cell Value with VBA If the Date Is Greater Than Today

Steps:

Open the VBA Window:

Go to the Developer tab.
Select Visual Basic in Code.

Insert a Module:

Go to the Insert tab.
Choose Module.

Enter the VBA Code:

Enter the following code.

Sub Modify_1()
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]>=R5C7,RC[-1]>=R6C7),""Delayed"",""On Time"")"
    Range("D5").Select
    Selection.AutoFill Destination:=Range("D5:D10")
End Sub

Save the code.

Run the VBA Code:

Close the Visual Basic window.
Press Alt+F8.
In the Macro dialog box, select Modify_1 in Macro name.
Click Run.

Output:

Code Breakdown

Sub Modify_1()

names the sub-procedure.

 Range("D5").Select

selects D5.

 ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]>=R5C7,RC[-1]>=R6C7),""Delayed"",""On Time"")"

The IF formula checks whether the value in C5 is greater than today. If the condition is met, the function will return “Delayed”. Otherwise, it returns “On Time”.

 Range("D5").Select
   Selection.AutoFill Destination:=Range("D5:D10")

The code fills the specified cells with the formula.

End Sub

ends the sub-procedure.

4. Change the Cell Color with VBA If the Date Is Greater Than Today

Steps:

Open the VBA Window:

Go to the Developer tab.
Select Visual Basic in Code.

Insert a Module:

Go to the Insert tab.
Choose Module.

Enter the VBA Code:

Enter the following code.

Sub cell_color()
For Each cell In Range("C5:C10")
If cell.Value > Date Then
cell.Interior.Color = RGB(0, 255, 255)
Else
End If
Next cell
End Sub

Save the code.

Run the VBA Code:

Close the Visual Basic window.
Press Alt+F8.
In the Macro dialog box, select cell_color in Macro name.
Click Run.

Output:

Code Breakdown

Sub cell_color()

names the sub-procedure.

For Each cell In Range("C5:C10")
If cell.Value > Date Then
cell.Interior.Color = RGB(0, 255, 255)
Else
End If
Next cell

loops from the declared first row (5) to the declared last row (10). If the cell value is greater than the current Date, it sets the color of the object.