Do you ever need to put a timestamp in an Excel spreadsheet? Maybe you are tracking time for billing purposes or maybe you just want to keep track of what happens when. If so, this post is for you! In the following steps, I will show how to create a timestamp with VBA code.
Create A Timestamp Using Excel Formulas.
There are other ways that you could accomplish creating timestamps in Excel. One of the most frequent questions and one of my most popular and visited blog posts is Create A Timestamp In Excel with Formulas.
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.
VBA Macro To Create Excel Timestamp.
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. That makes sense right?
So, here is a step by guide to inserting a timestamp 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 Excel 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 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
Walk Through Of Excel Timestamp Code.
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.
So, 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.
We’ve covered the basics of setting up a timestamp in Excel, and we hope that these tips will help you to create your own timestamps. If this post has been helpful to you, please share it with others who may be interested! Feel free to ask any questions below or at our website for more information. Finally, I would love know if this was useful for you so we can make future blog posts even better – thank you!
Other Suggested Excel Tips.
Paschal says
I tried this formula and it seems not to work for me. What can I do I need this for a presentation and basically I also want to know how to read a formula in excel
Paschal says
=IF(B1"",IF(A1="",NOW(),A1),"") that is the formula I tried
Barbara says
Pasha, try the formula in the article
=IF(B1<>“”,IF(A1=””,NOW(),A1),””)
regards
Barbara
Devin says
Not sure why I can’t get this to work – am I missing something in the formula? All I get is #NAME? when I enter this into a cell.
Barbara says
Try writing the formula into the cell not copying it. Sometimes the formatting from web page affects the copying and pasting.
Saulo Tavares says
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.
Kenneth Parker says
This works very easily. I would like to change the time format to 12 hour format and not 24 hour format. Is this possible?
Barbara says
Kenneth you can eaily format your cells as normal within the worksheet with Home Tab – Number Group and select your formatting
Regards
Barbara
Paul Mahoney says
How do I add a second timestamp macro – same line but different column?
Barbara says
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.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Cells(Target.Row, 2).Value = Date + Time
Application.EnableEvents = True
End If
If Target.Column = 5 Then
Application.EnableEvents = False
Cells(Target.Row, 6).Value = Date + Time
Application.EnableEvents = True
End If
End Sub
Bill Pratt says
I searched high, low and everywhere in between for a function that would work when using drop downs, formulas, etc. and this does it! Thank you!
Barbara says
Glad you liked it Bill
Regards
Barbara
Paul says
Barbara, I created a macro in worksheet 1, following the language, whereby when an entry is made in call A1, a time-stamp is inserted in B1. How do I add the language (duplicate in worksheet 2), so that when an entry is made in cell H1, a time-stamp is inserted in I1. I tried to replicate it and change the numbers in the macro to align with the column numbers, and it did not work?
Sonam says
even for the empty cells, the time value is generated. I want to set the value for the empty cell to be null. the value should be generated for cells with value only….please help?
Frank B. says
I used the code below to add a time stamp that won’t change to any selected cell. My sheet is set up to track total time, so I have to track time in and time out. This way I can choose my start or end time cell and stamp it with a click of a button assigned to run the macro. It uses the NOW() formula to initially time stamp then copies the cell and pastes it into itself as a value instead of the formula. This way it won’t over ride if I didn’t want it to.
ActiveCell.FormulaR1C1 = “=NOW()”
Range(ActiveCell.Address).Select
Selection.NumberFormat = “[$-F400]h:mm:ss AM/PM”
Range(ActiveCell.Address).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
DaveH says
How can I have the date and time stay on the date and time it was initially entered even though someone does edits on it? I’m working on a daily log book and need the date and time to appear automatically when someone enters an event, but with the above code, is someone makes an edit, it changes to the current date/time.
Fred Evans says
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
Barbara says
Fred It should say say ALT+F11 thanks for pointing out I have amended
Regards
Barbara
Martha says
Barbara,
I’m trying to create a timestamp on a worksheet page whenever the worksheet changes (gets updated). Ideally I would use =Now()… the only problem is that if I have two worksheets in the same file (workbook), then both update and I want them to function ONLY if the sheet the function is on changes. Is there a VBA for this?
Thanks!
Martha
Martha says
Barbara,
I need to add a timestamp to only one specific cell ‘B4’ if any of the other cells in the print area change. The code for the VBA is column per column, do you have any suggestions for what I have stated?
Thank you!
moxy says
This function works well. However it will populate as soon as i click on the first column. How can I get it to populate only when an Interger or letter is filled in?
Barbara says
Hello
Ensure that when you select your Worksheet and Change in the VB Editor, you did not choose SelectChange as this will cause the time stamp when you click cells in column A.
Regards
Barbara
Betty says
Thank you for the great and simple solution. I have the same experience as moxy above, with any click on 1st column resulting in a time stamp. When I go to VB Editor, I’m not seeing what you are talking about (SelectChange) or (Worksheet and Change). I don’t do VBA very often, so please forgive my ignorance. How do I find these settings/selections to change them?
Many Thanks,
BG
ALLAN says
Hi i want to know if i want the whole column let say Column A that whenever i press EnterKey the Date will get stampped in the Rows of that column. Thanks
Tami says
Hi Barbara! I tried your code and it worked – however……
I have 2 things I need to accomplish and am coming up at a loss with everything I have tried!
1st: I need to figure out how to have a cell populate the date/time for when ANY part of the sheet was last updated and get this information into 1 cell – not multiple cells per line
2nd: I need to see if there is a way to enter a date in one column and the remaining 11 months will auto update so that the months stay on a rolling basis. For example, if I enter Feb into the first column I want the others to update automatically in suite.
Hope that makes sense! Hope you can help!
Loizos Loizou says
Excelent code
Barbara says
You are welcome glad you found it useful!
Stephanie Smith says
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
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
Application.EnableEvents = False
Cells(Target.Row, 10).Value = Date + Time
Application.EnableEvents = True
End If
End Sub
Tiffany Kraft says
Hi Barbara – I am new to VBA so I would love your help with the following question.
Is it possible to time stamp when specific text is entered into a column vs any text?
Thank you!
AG says
Thanks, this was helpful! I repeated it 3 times to include changes among 4 columns.
Now what if I also wanted to generate a date+time stamp if formulas in my 4 columns are updated due to cell values (ranges B2:J3 and B6:C7) on another worksheet (Sheet2) changing?
I noticed the above code only runs if the cell values in the column are manually changed but not if they’re automatically changed from a formula calculation.
Thanks in advance,
AG
Amr says
Dear Barbra,
I used the above code & it works perfect. I need to integrate another code in addition to the above to lock the target cell after the time stamp is inserted. Can you help ?
Thanks, Amr
Barbara says
Hi
You could add an extra bit of code to lock the target cell after the time stamp is inserted. Try this code below from my blog post
http://www.howtoexcelatexcel.com/vba/macro-monday-how-to-automatically-lock-a-cell-after-a-value-is-entered/
The code is as below, just change the range of cells and follow the instructions in the blog post.
‘Macro by How To Excel At Excel’
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range(“B4")
If Not MyRange Is Nothing Then
Sheets(“Sheet1″).Unprotect Password:="mypassword"
MyRange.Locked = True
Sheets(“Sheet1″).Protect Password:="mypassword"
End If
End Sub
Amr says
Hi Barbara,
I tried to merge the code to lock the cells with the code for the time stamp but it seems that am doing something wrong. Any Chance that you can merge the two codes and post them here? Below is what I tried;
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.EnableEvents = False
Cells(Target.Row, 3).Value = Date + Time
Application.EnableEvents = True
End If
Dim MyRange As Range
Set MyRange = Range(“c4:c12”)
If Not MyRange Is Nothing Then
Sheets(“Sheet1″).Unprotect Password:=”1234”
MyRange.Locked = True
Sheets(“Sheet1″).Protect Password:=”123”
End If
End Sub
– Where B4:B12 is where the user type his/her name & C4:C12 is the location where the time stamp is populated.
Thanks in advance
Amr says
Hi.. i tried to integrate the tow codes together but it didn’t work. Can you help with posting both codes integrated together ?
Note: the user will make an entry in cells B4:B12 and the time stamp is populated in cells C4:C12
thanks in advance
Amr says
Hi Barbara,
I tried to merge the code to lock the cells with the code for the time stamp but it seems that am doing something wrong. Any Chance that you can merge the two codes and post them here? Below is what I tried;
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.EnableEvents = False
Cells(Target.Row, 3).Value = Date + Time
Application.EnableEvents = True
End If
Dim MyRange As Range
Set MyRange = Range(“c4:c12”)
If Not MyRange Is Nothing Then
Sheets(“Sheet1″).Unprotect Password:=”1234”
MyRange.Locked = True
Sheets(“Sheet1″).Protect Password:=”123”
End If
End Sub
– Where B4:B12 is where the user type his/her name & C4:C12 is the location where the time stamp is populated.
Thanks in advance
Amr says
Hi.. i tried to integrate the tow codes together but it didn’t work. Can you help with posting both codes integrated together ?
Note: the user will make an entry in cells B4:B12 and the time stamp is populated in cells C4:C12
thanks in advance
Amr says
Hi.. i tried to integrate the tow codes together but it didn’t work. Can you help with posting both codes integrated together ?
Note: the user will make an entry in cells B4:B12 and the time stamp is populated in cells C4:C12
thanks in advance