Return Expected Value If Date Is Greater Than Today in Excel

If you are looking for special tricks to know how to change cell value if the date is greater than today in Excel, you’ve come to the right place. There are numerous ways to change cell value if the date is greater than today in Excel. This article will discuss the details of these methods to change cell value if the date is greater than today in Excel. Let’s follow the complete guide to learn all of this.

How to Return Expected Value with Combined Formulas If Date Is Greater Than Today in Excel: 2 Examples

In the following section, we will use two effective and tricky examples to change cell value if the date is greater than today in Excel. For a date greater than today, we will perform multiple tasks: set the value of the cell, and delete the value This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

1. Set Cell Value If Date Is Greater Than Today

Here, we will demonstrate how to set cell value if the date is greater than today. A combination of the TODAY and IF functions are the best approach to set cell value if the date is greater than today. Suppose, we have a dataset of products along with their delivery date. Let us consider the deadline for delivery as today 8/28/2022. For you, it will be the date on which you are practicing. Now we will figure out the delivery status of all the products with the following steps.

Let’s walk through the following steps to set the cell value if the date is greater than today.

📌 Steps:

• First, select cell D5 and type the following formula:

• Next, press Enter.
• Here, in cell D5, we get the delivery status for the product mouse in ‘On Time’.

• Then, drag the Fill Handle icon to fill the other cells with the formulas.
• Finally, the delivery status for all the products looks like the below figure.

2. Delete Cell Value If Date Is Greater Than Today

This example shows how to delete a cell value if the date exceeds today. In order to delete cell values if the date is greater than today, it is best to combine TODAY and IF functions. Let’s say we have a dataset of products with their delivery dates. Let us consider the deadline for delivery as today 8/28/2022. The following are the steps we will take to determine the delivery status of all the products.

Let’s walk through the following steps to delete the cell value if the date is greater than today.

📌 Steps:

• First, select cell D5 and type the following formula:

• Next, press Enter.
• Here, in cell D5, we get the delivery status for the product mouse in ‘On Time’.

• Then, drag the Fill Handle icon to fill the other cells with the formulas.
• Finally, the delivery status for all the products looks like the below figure.
• This is how you will be able to delete the cell value if the date is greater than today.

Examples with Excel VBA to Return Expected Value If Date Is Greater Than Today

The following are four excellent and tricky examples of changing cell values if today’s date is greater than today’s. In case of a date greater than today, we will perform multiple tasks: set the value of the cell, delete the value, change the color of the cell, and finally modify the value of the cell. Detailed information about these methods can be found in this section.

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

If you want to set cell value if the date is greater than today in Excel, you need to use the help of VBA. Microsoft Visual Basic for Applications (VBA) is Microsoft’s Event Driven Programming Language. To use this feature you first need to have the Developer tab showing on your ribbon. Click here to see how you can show the Developer tab on your ribbon. Once you have that, follow these detailed steps to set cell value if the date is greater than today using VBA code in Excel.

📌 Steps:

⧭ Open VBA Window:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

⧭ Insert Module:

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created.

⧭ Insert VBA Code:

• Now select the module if it isn’t already selected. Then write down the following code in it.

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

• Next, save the code.

⧭ Run VBA Code:

• Afterward, close the Visual Basic window. After that press Alt+F8.
• When the Macro dialogue box opens, select Set_1 in the Macro name. Click on Run.

⧭ Output:

• Therefore, the delivery status for all the products looks like the below figure.

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

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

Using VBA, you can delete a cell value if the date is greater than today in Excel. Follow these detailed steps to delete cell value if the date is greater than today using VBA code in Excel.

📌 Steps:

⧭ Open VBA Window:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developers tab on your ribbon. Then select Visual Basic from the Code group.

⧭ Insert Module:

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created.

⧭ Insert VBA Code:

• Now select the module if it isn’t already selected. Then write down the following code in it.

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

• Next, save the code.

⧭ Run VBA Code:

• Afterward, close the Visual Basic window. After that press Alt+F8.
• When the Macro dialogue box opens, select Delete_1 in the Macro name. Click on Run.

⧭ Output:

• Therefore, the delivery status for all the products looks like the below figure.
• This is how you will be able to delete the cell value if the date is greater than today.

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

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

The cell value can be modified using VBA in Excel if the date is greater than today. Follow these detailed steps to modify the cell value based on the end’s deadline if the date is greater than today using the VBA code in Excel.

📌 Steps:

⧭ Open VBA Window:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

⧭ Insert Module:

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created.

⧭ Insert VBA Code:

• Now select the module if it isn’t already selected. Then write down the following code in it.

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

• Next, save the code.

⧭ Run VBA Code:

• Afterward, close the Visual Basic window.
• Then, you have to modify the end date (cell G6) of the dataset according to your preference.
• After that press Alt+F8.
• When the Macro dialogue box opens, select Modify_1 in the Macro name. Click on Run.

⧭ Output:

• This is how you will be able to modify the cell value based on the end’s deadline if the date is greater than today.
• The Status column statement will automatically change if the end date is changed.

Sub Modify_1()

 Range("D5").Select

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

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

End Sub

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

In Excel, if the date is greater than today, you can change the color of a cell value using VBA. Follow these detailed steps to change the cell color if the date is greater than today using the VBA code in Excel.

📌 Steps:

⧭ Open VBA Window:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

⧭ Insert Module:

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created.

⧭ Insert VBA Code:

• Now select the module if it isn’t already selected. Then write down the following code in it.

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

• Next, save the code.

⧭ Run VBA Code:

• Afterward, close the Visual Basic window. After that press Alt+F8.
• When the Macro dialogue box opens, select cell_color in the Macro name. Click on Run.

⧭ Output:

• This is how you will be able to change cell color if the date is greater than today.

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

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

Conclusion

That’s the end of today’s session. I strongly believe that from now, you may learn how to change cell value if the date is greater than today in Excel. If you have any queries or recommendations, please share them in the comments section below. Keep learning new methods and keep growing!

<< Go Back to If Date | Formula List | Learn Excel