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.

  • 1
    Could you just copy that column at the end of the day and paste it into a new column using paste.special: values? That way you have the original column ready for tomorrow... just a suggestion as I don’t know what your data looks like... – Solar Mike Jan 22 '18 at 23:23
  • Write a VBA function that returns the required due date. Then in the worksheet's OnChange event, watch for the user to enter the letter d into your Due Date column. When this happens, run the function and replace the cell's value with the calculated date. Then any time you want a static, but correctly calculated due date, type "d" and it will be entered for you. – I say Reinstate Monica Jan 23 '18 at 1:41
  • I've modified my ANSWER, find two solutions, will help you to fix the issue. – Rajesh S Jan 23 '18 at 13:26

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.

| improve this answer | |
  • 1
    My comment gave your solution 2 a while ago.... – Solar Mike Jan 23 '18 at 9:17
  • Sorry @SolarMike,,, i've had not noticed it and mine is lil different, I suggested to Paste in the Same Cell. – Rajesh S Jan 23 '18 at 9:53
  • 1
    So, your way actually removes the formulae so it won't work tomorrow... – Solar Mike Jan 23 '18 at 9:54
  • @SolarMike, Yes, suggested 3 options are Converting Today() Volatile to Static and it was the OP's demand, since Static Formula Never Updates. – Rajesh S Jan 23 '18 at 10:51
  • 1
    So you did not understand « and so on and so forth » ie the OP would like it to continue working tomorrow in the fashion it has been designed - you want it to work once then never again - it needs to work once each day and save that day’s information THEN be ready to repeat the next day... – Solar Mike Jan 23 '18 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.