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.


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.


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

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.

Read More: Return Expected Value If Date Is Between Two Dates in Excel


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.

Set Cell Value If Date Is Greater Than Today in Excel

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:

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

  • 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.

Return Expected Value If Date Is Greater Than Today in Excel

🔎 How Does the Formula Work?

  • TODAY()

This part takes the date of the present day.

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

This formula will check whether the value of cell C5 is greater than today or not. If the condition is met, the function will return “Delayed”. Otherwise, it returns  “On Time”.


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.

Delete Cell Value If Date Is Greater Than Today in Excel

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:

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

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

applying IF Formula

  • 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.

🔎 How Does the Formula Work?

  • TODAY()

This part takes the date of the present day.

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

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


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.

Set Cell Value with VBA If Date Is Greater Than Today 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.

open VBA window

⧭ 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.

Run Macro

⧭ Output:

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

🔎 VBA Code Explanation:

Sub Set_1()

First of all, provide a name for the sub-procedure of the macro.

 Range("D5").Select

In a later step, select cell D5.

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

In the first line of this piece of the code, after selecting the cell we inserted the IF formula. This formula will check whether the value of cell C5 is greater than today or not. 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 will fill the remaining cells in the specified region with the formula from the previous cell.

End Sub

Finally, end the sub-procedure of the macro.


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.

Delete Cell Value with VBA If Date Is Greater Than Today 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.

insert module

  • 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.

get Expected Value If Date Is Greater Than Today in Excel

🔎 VBA Code Explanation:

Sub Delete_1()

First of all, provide a name for the sub-procedure of the macro.

 Range("D5").Select

In a later step, select cell D5.

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

In the first line of this piece of the code, after selecting the cell, we inserted the IF formula. This formula will check whether the value of cell C5 is greater than today or not. 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 will fill the remaining cells in the specified region with the formula from the previous cell.

End Sub

Finally, end the sub-procedure of the macro.


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.

Modify Cell Value with VBA If Date Is Greater Than Today 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.

insert module

  • 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.

show Expected Value If Date Is Greater Than Today in Excel

🔎 VBA Code Explanation:

Sub Modify_1()

First of all, provide a name for the sub-procedure of the macro.

 Range("D5").Select

In a later step, select the cell D5.

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

In the first line of this piece of the code, after selecting the cell, we inserted the IF formula. This formula will check whether the value of cell C5 is greater than today or not. 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 will fill the remaining cells in the specified region with the formula from the previous cell.

End Sub

Finally, end the sub-procedure of the macro.


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.

Change Cell Color with VBA If Date Is Greater Than Today

📌 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.

inserting module

  • 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.

show the output

🔎 VBA Code Explanation:

Sub cell_color()

First of all, provide a name for the sub-procedure of the macro.

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

Now, starts looping from the declared first row (5) to the declared last row (10). If the cell value is greater than Date, then it will set the color of the object.

End Sub

Finally, end the sub-procedure of the macro.


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo