The formula solution, whilst very useful does not suit everyone and has some restrictions in itself. If you are brave enough to take on a small bit of VBA code, we can create and event handler macro which is a good alternative solution to using the formula method which I find is slightly more robust and flexible.

This was a an alternative solution for a subscriber rather than the formula date and time stamp method. He wanted the timestamp to update if he decided to overwrite the contents of the target cells again and again. This I believe gives that solution.

There is no need to insert a module in, or use a module in this piece of code, it is written directly to the worksheet you want the timestamp to appear on.

So, here is a step by guide to inserting a time data stamp into Excel with VBA. You can download a sample workbook here to work through this example.

We are assuming that the date time stamp will be entered into Column B when an entry is made into Column A.

Open up your Excel worksheet and hit ALT +F11 to open the Visual Basic Editor. ( if you need to enable the Developer Tab you can find out more here)

Double Click on the worksheet where your timestamp is to appear- in this example it is Sheet 1.

In the left hand drop down box select Worksheet and in the right drop down box select Change. (This will create a macro that runs when the worksheet changes), and the Value Target As Range means any cell we choose to apply this to.

We want to then type the following code underneath the First Line that Excel generated for us.

If Target.Column = 1 Then

Application.EnableEvents = False

Cells(Target.Row, 2).Value = Date + Time

Application.EnableEvents = True

End If

Let’s talk through the code. It is an If Then Statement with the following meaning…..

If The Target Column 1 changes Then we want the cells in the Target Row in the Second Column to change to the value of Date and Time. Easy huh?. Now we have a couple of extra lines of code in there, the Application.EnableEvents. By adding these in we ensure that the macro does not run on and on, by turning off the macro then turning it on as required.

This solution allow overwriting of previous entries, and will update with the latest date time stamp. So if you find the Formula way of inserting a timestamp into an Excel worksheet then these few little lines of code may just be your solution.

The result of this piece of code looks like this.

Let me know if you find this piece of code more useful than the formula solution in the comments below.

I’ve tried your code and it works. Could you please show me how do this operation by a CommandButton_Click?
If I click, a timestamp is stored at a specific cell, If I hit again, the next row gets the new value.

Paul, just repeat the routine in the VBA window and amend the columns you want to include also. In the exmaple below I want to add a timestamp in Column 5 if Column 6 is amended as well as the origional Column 1 and 2 respectively.

hello Barbara-
Please help
I am trying to follow all direction but the very first instruction
stymies me
You say “hit F11 to open visual basic editor”
When I hit f11 a chart opens ?
then I have to go to the bottom tab to delete the chart??
Please help
Regards
fred evans

Hi Barbara, I’m currently using Excel 2016 and I’ve tried this code several times but to no avail. I want it to work so that when I enter data into column 8 the date/time appear in column 10, so I changed the formula to the one below. I’ve also confirmed that I’ve chosen “worksheet” in the left drop-down and “change” in the right drop-down box, and that I’ve also chosen the correct spreadsheet in the left hand side. Any help you can provide would be greatly appreciated!!!
Thanks,
Stephanie

Your email address will not be published. Required fields are marked *

Comment

Name *

Email *

Website

Follow How To Excel At Excel

Need Answers To Excel Questions Like These?

*How Do I Create A Timestamp In Excel?
*I Want To Change The Width Of The bars On My Excel Chart
*How Can I Find Out The Length Of My Text in Excel?
Click the link below to receive more Excel tips' and my Free Ebook

Sign Up For My FREE Excel Tips Newsletter and receive your own E-book of my Top 50 Excel Tips.