How to insert update date or timestamp in a cell

A few months ago a user asked this question in Excel Help Forum:

If you have two columns, is it possible to have column B put in the date that data was added to column A?

I have replied to the question in the forum at that time, and I have noticed since then that other users are asking the same question. For that reason I thought it might be a good idea to elaborate on the subject here.

To insert an update date or a timestamp in a cell, you need to use a worksheet event called “Worksheet Change”. Just follow these steps:

1. Switch to visual basic editor by clicking Tools –> Macro –> visual basic editor.

2. Click on the name of your sheet in project explorer at your left.

Timestamp1

3. On the right pane select “Worksheet” from the left drop down and select “Change” from the right drop down.

4. Now you are in the Worksheet_Change event.

5. Copy this code between “Private Sub Worksheet_Change” and “End Sub”.

Col = Left(Target.Address, 2)
If Col = “$A” Then Target.Offset(0, 1) = Now

Timestamp2

Now whenever you change a cell in column “A” the next cell in column “B” will be updated with date and time.

You can change the code to suit your own needs, but the main idea is that you have to use the “Worksheet Change” event for this purpose.

Comments

  1. Michal says:

    Hi, thank you for it.
    And is there a chance that it would work also for cells which are automatically updated from internet? This makro is working only for the cells which I update. I have an excel sheet that is taking prices from internet. but for this prices this makro is not givving me the timestamp.
    Thank you for your help.
    Michal

  2. admin says:

    This macro will work if you update the cell manually and not through a formula or so. I am not sure if there is an easy way to resolve your problem.
    I suggest that you post this problem into Excel Help Forum. You may find an answer there. Here is the link:

    http://www.excelforum.com/

  3. Graig Pitarresi says:

    You saved me a great deal of time researching for my class assignment. I’m in your debt forever!

  4. Ze Mane says:

    Hi,

    Thank you for your nice article!

    I have a question: Can I have a cell that always contains the date when the spreadsheet was last saved?

    The difference here is that it is not related to a particular cell, but to the whole spreadsheet.

    I’d like to have a note at the foot of my spreadsheet saying
    “Last updated on: 06/23/2010″
    and the date would update every time I save the spreadsheet.

    Thank you again,

    –Ze Mane

  5. admin says:

    Again you have to use a macro, but in your case you have to put your code in the workbook_before_save event.

Trackbacks

  1. [...] Vote How to insert update date or timestamp in a cell [...]