Today() ain’t so bad…

Finding non-VBA alternatives for the volatile TODAY and NOW functions is tricky, but here’s an example of some creative workarounds for the Evil Boss’ report generator.
 
Non volatile today 2
 
 
That last one that uses a dropdown I pulled from a at the ExcelHero LinkedIn group . It’s pretty clever: what you do is create a small Table somewhere, and populate it using the naughtily volatile =TODAY() function, as well as the words “Please choose…” .
 
Please Choose
 
(An alternate wording to “Please choose…” would be “Choose wisely…”).

Then you point a dropdown list at that Table. And then you assign the name TodaysDate to the cell that the dropdown lives in, and use that instead of TODAY() throughout the rest of your workbook.

The beauty of dropdowns is that while they may reference that volatile function, as soon as the Evil Boss makes a choice, the choice gets written to the cell as a string, and not as a reference to that volatile function. So it IS today’s date, but it is NOT volatile. Of course, you need a big obnoxious message pointing at it so that:

  1. The Evil Boss remembers to fill it out today; and
  2. The Evil Boss also remembers to update it tomorrow

But obnoxious is my middle name.

5 thoughts on “Today() ain’t so bad…

  1. That’s a fun read…thanks…and an opportunity to get me thinking about some other ways to solve the problem. Double thanks. We used to call this “Idiot Proofing”. Now we call it “Director Proofing”, as if there were a difference.

    That said, here are a couple of other ideas…

    If it is always a new workbook and not an update of a current work book make a template where the cell with the date says “Click Me!” and is Named “ClickMe”. Then in the worksheet module put this code.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range(“ClickMe”)) Is Nothing Then
    If Intersect(Target, Range(“ClickMe”)) = Range(“ClickMe”) _
    And UCase(Range(“ClickMe”).Value) = “CLICK ME!” Then
    Range(“Clickme”).Value = Format(Now(), “m/d/yyyy”)
    End If
    End If
    End Sub

    Or, if the date should be updated when changes are saved on a new day, try this, using “TheDate” was the Name for the cell this time.
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not ThisWorkbook.Saved Then
    If Range(“TheDate”) < Format(Now(), "d/m/yyyy") Then
    Select Case MsgBox("Change to today's date?", vbYesNoCancel, "Gentle Reminder")
    Case vbCancel
    Cancel = True
    Case vbYes
    Range("TheDate").Value = Format(Now(), "d/m/yyyy")
    Case vbNo
    End Select
    End If
    Else
    Cancel = True
    End If
    End Sub

  2. I recently discovered that Bloomberg provide their own BToday() function. It provides the current date via DDE – this means it’s not volatile but automatically ticks over at midnight (if you want it to). Might be useful for those of you in the financials world.

Leave a Reply

Your email address will not be published. Required fields are marked *