Insert Date Time Stamp with VBA

Inserting date time in Excel using VBA is as simple as writing a formula in a Cell.

The VBA timestamp code is:

Range(“A1”).value = Format(Now, “mm/dd/yyyy HH:mm:ss”)

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.

Show date and time whenever a change is made using VBA.

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.

1

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.

Run Macro When A Change is Made (Worksheet Change Event Handler)

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.

2

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.

3

Now adjacent to worksheet drop down, you see another drop down. It contains a lot of event handlers. For now, select “change”.

4

Now copy the above code here.

5

And it is done. Get back to your sheet and check it.

Code Explanation:

    • The code is written on sheet1 by double-clicking on it. It means your code belongs to only this sheet.
    • We used “Change” event handler of “Worksheet” to trigger our VBA Code.
    • Private Sub Worksheet_Change(ByVal Target As Range)

      This is the fixed and default subroutine name of a change event handler.

    • We want our code to run change is made only in column A.
    • If Not Intersect(Target, Range("A:A")) Is Nothing Then

      This line checks if the changed cell is in column A or not.

    • Now, if the above line returns True, then these lines are executed
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.

  • And the last three lines just close their respective block. And it’s done. Now you have a sheet that gives an exact time of change made in a cell of column A.

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.

Users are saying about us...

  1. 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?

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube