Inserting date time in Excel using VBA is as simple as writing a formula in a Cell.
The VBA timestamp code is:
This code will show the current date and time in cell A1. And yeah, that is it. it's done.
The “Now” function returns current date and time, and “Format” function formats the output into "mm/dd/yyyy HH:mm:ss" format.
Let's say you have prepared a sheet. In this sheet, you want time to be shown in column B whenever someone does an entry Column A.
Now we need a VBA timestamp code that runs every time when change is made in column A.
The below code gets the work done easily.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing ThenOn Error Resume Next
If Target.Value = "" ThenTarget.Offset(0, 1) = ""
Else
Target.Offset(0, 1).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
End If
End If End Sub
To run this code, open VBA and double click on the sheet in the project window on which you want to show timestamp.
Now we have to use VBA event handler since we want to run our code to run every time when a change is made.
From the drop-down above your code, select Worksheet.
Now adjacent to worksheet drop down, you see another drop down. It contains a lot of event handlers. For now, select “change”.
Now copy the above code here.
And it is done. Get back to your sheet and check it.
Private Sub Worksheet_Change(ByVal Target As Range)
This is the fixed and default subroutine name of a change event handler.
If Not Intersect(Target, Range("A:A")) Is Nothing Then
This line checks if the changed cell is in column A or not.
On Error Resume Next If Target.Value = "" ThenTarget.Offset(0, 1) = "" ElseTarget.Offset(0, 1).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
The first line ignores error pop and runs these two lines. If you omit On Error Resume Next you will see error popping up when you delete a value in column A.
If Target.Value = "" Then
:- This line checks if a cell in column A is blank. If yes then
Target.Offset(0, 1) = ""
:- This line deletes adjacent cell value.
And if False
Else Target.Offset(0, 1).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
:- This line enters the current time and date in adjacent of A column.
You may have noticed that there are several event handlers in the drop-down list. Play around them. Try to figure out what each event handler works. And if have any difficulty, write it down in the comments section.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Code enters the date in column f based on any changes made in a,b,c,d,e. Only issues I am having is column e has comments that get deleted after adjustments are made to the rest, but the date deletes too, even though the other columns have been adjusted. Is there a way to stop the date deleting?
Hi, this works if you enter 1 by 1, meaning the date&time will appear when you enter singular. But what if i have a group of say 6 in a column B and i copy them simultaneously in column A - then i don't see the date&time next to them (though Date&Time should be equal for all of them). Can you help?
I want the date to insert into C2, but when I change the range to "C:C" it doesn't insert anything. How would I do this, using the same code above?
Hi Anna,
Make sure that you are writing the code in the sheet you want to have the stamp. Do not use a global module.
If it doesn't help, let me see your code.
If you wish to have the date in C2, pls change Target Offset as "Target.Offset(0,2)".
The range where you changing as C:C is for the Data source upon changing which the result would give you in Date & Time format. Hope it helps you.
Cheers!
This code is wonderful. However, I am looking to create a sign-in sheet that not creates a time when a customer signs in, but it will create a current time when the clerk enters their name into the sheet and when creates a current time when the clerk customer signs out. I guess I'm having trouble duplicating the code for different columns. Help.
Hi Kim,
As i can understand that you want to a time stamp when clerk writes his name a column and again in a different column when he signs out. Right?
So let's say your clerks enter there name in column A and you want the time of name entered in Column B. You want to enter time in column D when clerk signs out in colum c, for that use this code.
Actually, we are using worksheet change event here. And you can only have one event of one type on one sheet. So you need to edit the code inside the same event. Use the below code.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing ThenOn Error Resume Next If Target.Value = "" ThenTarget.Offset(0, 1) = "" Else Target.Offset(0, 1).Value = Format(Now, "mm/dd/yyyy HH:mm:ss") End If End If If Not Intersect(Target, Range("C:C")) Is Nothing ThenOn Error Resume Next If Target.Value = "" ThenTarget.Offset(0, 1) = "" Else Target.Offset(0, 1).Value = Format(Now, "mm/dd/yyyy HH:mm:ss") End If End If End SubYou can read about the events in Excel here.
https://www.exceltip.com/events-in-vba/the-worksheet-events-in-excel-vba.html
Thanks for this wonderful code. Would be great help if you can share the VBA code for below mentioned requirement ...
Using this code if we insert anything in column A2 or A3 or A4 then in column B2,B3,B4 date and time will be inserted automatically. At the same time if i put anything in column "N" then column "O" must filled the date and time automatically (Doing this i want to capture the start time and end time)
I am using this code successfully, however I would like to advance one step further. When a cell value changes, I would like vba code to insert a cell comment with the date/time the cell value changed. this will eliminate a column in my spreadsheet. Can you help?
So, cute, I download the example, and now I have password protected modules in my VBA....care to share to get these off?
Just close excel completely and re-open