How can I make a volatile Excel formula static, specifically a formula containing the TODAY function?

Question

I have a task log that I created at work to prioritize my daily to-do's.

In one column I've added a formula to automatically calculate Covey's quadrant numbers based on whether or not I've assigned a task as urgent or not urgent and important or less important (which populates as 1, 2, 3 or 4). I also have a column to assign a due date. I want to automatically assign a due date and I wrote a formula to do so, but the TODAY function means that tomorrow, my due dates will change. Here is what I am looking to accomplish:

1. I want the due date to calculate automatically based on the quadrant in column G and today's date.
2. I want the due date to remain static when I open the workbook tomorrow.
3. When I enter a new task tomorrow, I want the due date to automatically calculate again using the TODAY function and then again remain static the next day, as so on and so forth.

I have been researching this topic online furiously for some time now to no avail. I have found many recommendations to use CTRL+ to automatically enter today's date into a cell, and that is not what I am looking for.

Please review my formula below and let me know if there is any way to accomplish my goals, macro, vba, formula or otherwise. Please be very detailed in your directions as I am just learning about macro and vba.

=IF(ISBLANK(E4),"",IF([@QUADRANT]=1, TODAY()+1,IF([@QUADRANT]=2, TODAY()+7,IF([@QUADRANT]=3, TODAY()+3, IF([@QUADRANT]=4, TODAY()+30)))))

Answer 1

I would like to suggest TWO Solutions, to achieve the Goal.

Solution 1:

Use these VBA codes as Module. First will convert the Current Date in cell A1 into a Static Date Value while Saving the Workbook and Second Macro will Convert the Static Date Value into Volatile.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    With Sheet1.Range("A1")
        .Value = .Value
    End With

End Sub


Private Sub Workbook_Open()

     With Sheet1.Range("A1")
        .Value = "=Today()"
    End With

    End Sub

Solution 2:

In place of two different Macros you can create a Toggle Button to Convert the Volatile Date into Static and again to Volatile Date value.

    Private Sub CommandButton1_Click()

     If CommandButton1.Caption = "Convert Static" Then

      With Sheet1.Range("A1")
      .Value = .Value
      End With

   CommandButton1.Caption = "Convert Volatile"

   ElseIf CommandButton1.Caption = "Convert Volatile" Then

    With Sheet1.Range("A1")
            .Value = "=Today()"
        End With

  CommandButton1.Caption = "Convert Static"
  End If

  End Sub

NB: Follow these steps to accomplish the Job.

1. In the sheet 1 where you execute your Formula, in Cell A1 write this Formula = Today().
2. For the solution 1, Press Alt+F11 to open the VBA Editor, find the "Microsoft Excel Objects".
3. Right Click the Mouse on it, find Insert then Module.
4. Copy & Paste both the Macros.

For Solution 2, follow these steps.

1. In Sheet 1, Click the Developer Tab, find Design Mode, left to it is Insert.
2. Find ActiveX Controls, the first is Command Button, Click it and draw the button in blank area.
3. Right Click the Command button, Find Properties, Find Caption and change it to Convert Static.
4. Return to Sheet, double click the Command button, VBA editor will be open.
5. Copy and Paste the VBA Code, go to File menu, hit Close and Return to Excel.
6. Again click the Design button on top to turn it deactivate.
7. Click the Command Button, will change Volatile Date value in Cell A1 to Static, and change the Caption of Command Button also to, Convert Volatile.
8. Click it again when you want to change Cell value A1 to Static.

Remember, on your each Click the Macro will convert Date in A1 cell Volatile to Static to Volatile.

Note, in YOUR FORMULA use Cell A1 instead of TODAY().

You are free to write =Today() Formula in any BLANK CELL but don't forget to modify the Cell Address accordingly.

I'm sure this help you.

Answer 2

Here is a non-macro answer:

1. Allow circular references by going to File>Options>Formulas check "Enable iterative calculation" (for Mac it is Excel menu>Preferences>Calculation check "Use iterative calculation").
2. Put this formula in H1 if the number you want to use for your date calculation will be in G1: =IF(ISNUMBER(G1);IF(H1="";NOW();H1);"")

• Note1: switch ; to , depending on language.
• Note2: G1 must be initially empty when you input the formula.

What does the formula do?

The first IF statement checks if there is a number in G1:

1. if there is no number in G1, then H1 is kept blank ("").
2. if there is a number in G1, a second IF statement is evaluated.

The second IF statement:

1. checks if H1 is blank ("").
2. if H1 is blank, return NOW(), i.e. the serial number of the current date and time.
3. if H1 is not blank, then it already has the NOW() value, so return H1.