If you want a timestamp in an Excel workbook, you can use the NOW() function (or TODAY() if you don’t want the time part). However, these functions are ‘volatile’, meaning that they recalculate themselves whenever any change is made to the workbook. This isn’t usually what we want: what we typically want is a calculate-once funcion.
If we want a single timestamp for creation of a workbook, we could have a =NOW() formula in the workbook template, and then on opening a new workbook do a Copy-Paste Special Values for that cell. In the ThisWorkbook module, we could have:
Private Sub Workbook_Open() Range("Timestamp").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False Application.CutCopyMode = False End Sub
However, there’s a simpler way: define a function that can be used in a formula, which uses the VBA Now() function, rather than the Excel NOW() function. In a public module of the template or your PERSONAL.xls workbook define:
Public Function TIMESTAMP() As Date TIMESTAMP = Now End Function
Now, where we want a timestamp, we can simply enter the formula =TIMESTAMP(). This is calculated when the formula is entered from edit mode, or when it is filled into a cell (e.g. filled down).
We can timestamp data values as they are entered, say in column A, by having this formula in column B:
That’s an empty string as the ‘else’ argument.
A timestamp even disappears if we delete a data value from column A, and re-appears with an updated value if a data value is re-entered.