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.

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

  3. My Co. uses onedrive for everything and needs multiple users sharing excel files meaning VBA won’t work for us until MS finds a way for sharepoint/onedrive to support multiuser .xlsm files.

    With the newer dynamic array functions I’ve eliminated volatile functions from all our workbooks apart from…. TODAY()

    I might try the drop down option above but I just don’t trust users to do it daily. I use a Named range for TODAY() so that if I find a workaround later it’ll be easy enough to deploy / test alternates.

    Our project budget XL runs a bit laggy, I think it’s between unpivoting massive timesheet datasets joined using delimited strings and FILTER/CHOOSE then extracted using FILTERXML and that last pesky volatile TODAY() function. It’s not a dealbreaker but I sure would love to eliminate that lag. Fingers crossed for a non-volatile TODAY() in the future

  4. @Marko
    I don’t live in a multiuser environment so I don’t know if this makes sense, but can’t you put your VBA in an .xlam Add-In that is accessible from each user’s .xlsx file? I assume the Add-In can be located in OneDrive. My Excel Toolbox has references to some Add-In articles.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.