Microsoft Excel offers two common functions to refer to the current date and/or time: Today() and Now().

This short note describes the risks associated with the use of these functions and suggests that they be used with care.

Firstly, both of these functions are volatile. To understand why this is bad, we need a brief diversion into the way spreadsheets work. Excel maintains a list of cells which are impacted by changes in each cells value. In a simple financial model, changing the price may change revenue and profit lines, but seeing no link, Excel will not seek to recalculate the expense section of your workbook. This saves time. However, introducing volatile functions means that the spreadsheet will now recalculate these functions. Any cells which may rely on them. This will slow down your spreadsheet.

Secondly, and most importantly, these functions can be misleading if used in the way they are meant to be used. For example, a spreadsheet which generates a daily report could save the user time by using the TODAY() function – no need to update the day every day. However, this will then create issues unless it is based on a lookup of the date. Static numbers or formulae will lead to confusion.

Updated

The date look up table was imagining a report which looked up a figure from a daily time series.  In that case, the entire report will update with the latest day’s data, potentially being useful.  I was discouraging the attempt to have yesterday’s report being rendered confusing because the headings are now for today but it still holds yesterday’s results.