Finding alternatives for the TODAY and NOW functions is tricky, but here’s an example of some creative workarounds for the Evil Boss’s report generator.
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. 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:
- The Evil Boss remembers to fill it out today; and
- The Evil Boss also remembers to update it tomorrow
But obnoxious is my middle name.
