Jtdylktuy

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)))))

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.