With a Macro assigned to a Button you can copy and Paste Special/Paste Special/Paste (uncheck all but “Date & time”) of a dynamic “=NOW()” cell to another cell where it remains static, only changing when the Button is clicked. Just click the Button before saving the file.
Here’s how I save “Last Modified” using Macro.
- Start Macro Record
- copy the cell that has the formula =NOW().
- Move to a cell (that is formatted for date or time as you want) and next to a cell Labeled “Last Modified,” and Paste VALUES ONLY.
- Hit the SAVE button THEN STOP RECORDING.
I run this Macro for all my spreadsheets. Of course, the =NOW() cell updates every time you open or make a change.