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()
    Selection.PasteSpecial Paste:=xlValues, _
                Operation:=xlNone, SkipBlanks:=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
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.


10 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 + ;
    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.


  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

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


    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

    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!

  9. 9 hackintosh September 16, 2014 at 7:59 pm

    I have been exploring for a little bit for any high quality articles or
    blog posts in this sort of area . Exploring in Yahoo I at last
    stumbled upon this site. Studying this info So i’m happy to show that I have
    a very excellent uncanny feeling I discovered just what I needed.
    I so much definitely will make certain to don?t put out of your
    mind this website and provides it a look regularly.

  10. 10 Mable October 4, 2014 at 1:40 am

    Bookkeeping is a daunting task; many companies have failed due to poor bookkeeping.
    Bookkeeping is a name given to the process that is
    undertaken to maintain records of the business transactions done on a daily basis.
    Bookkeeping and accounting are the simplest processes that
    a company can contract out to another. This can be achieved
    by the giving out bookkeeping franchise to a number of different people at different locations.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

February 2009
« Jan   Mar »

%d bloggers like this: