Excel Timestamp

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:

=IF(NOT(ISBLANK(A2)),PERSONAL.XLS!TIMESTAMP(),””)

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.

About these ads

8 Responses to “Excel Timestamp”


  1. 1 Fred April 28, 2009 at 4:11 pm

    Thanks for the your TIMESTAMP code. Very simple and straightforward to use. But I have a suggestion that might help newbies like myself: add a short paragraph that walks us through the steps of adding a UDF to a worksheet. I had to go elsewhere to figure out how to do it and then went back to your site to get the code. But thanks a million, works great and is just what I needed.

  2. 2 el chief July 17, 2009 at 6:27 pm

    CTRL + ;
    SPACE
    CTRL + SHIFT + ;

  3. 3 Roy MacLean July 19, 2009 at 10:57 am

    @el chief:
    I think you’re missing the point: with the VBA function, the timestamping is automatic. When an entry is made in column A, a timestamp is automatically inserted in column B (or wherever: it could even be on another worksheet). When an entry is deleted, its timestamp disappears; if an entry is re-entered, its timestamp is updated. Simple! Relying on manually entered timestamps is questionable.

    /Roy

  4. 4 el chief September 25, 2009 at 6:21 am

    ya, Roy, I got that.

    I was suggesting a manual way to put in a hard-coded timestamp

  5. 5 Brent January 10, 2011 at 11:36 pm

    Thank You!

    I am new at Excel but have been trying to automate some data gathering where I teach high school. This works and is exactly what I needed!

    Thanks again
    /BKP

  6. 6 Jim September 12, 2011 at 8:58 pm

    Hello,

    I tried entering the above in the VBE and when I saved the workbook and reopened, the timestamp values changed. Note I had to enable macros because there are other UDF’s in the sheet that need to run. How do I get around this?

  7. 7 Roy MacLean September 16, 2011 at 3:41 pm

    Jim,
    A timestamp formula, as above, recalculates only when the input cell changes – just like any other non-volatile formula. So the only way that the timestamp could change on Workbook Open is if you are changing the input data using another macro, or you’re forcing a recalculation using Application.Calculate or Worksheet.Calculate.

  8. 8 Nicola Page September 26, 2011 at 9:52 am

    This is a beautiful solution – simple and easy to apply. Many thanks!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




February 2009
M T W T F S S
« Jan   Mar »
 1
2345678
9101112131415
16171819202122
232425262728  

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: