HH:MM time stamping in Excel worksheet

Question

I am using the following code to put a time stamp in column 1 whenever new values are added to a row of data within a worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Or Target.Column > 11 Then Exit Sub
    With Application
        .EnableEvents = False
        Me.Cells(Target.Row, 1) = Now
        .EnableEvents = True
    End With
End Sub

I am a bit of a VBA beginner, can anyone tell me how I can ensure the time-stamped data is in hh:mm format? At present it is including the date.

Highlight the column and then select `Format` and select `Custom` and enter `hh:mm` - To get the code for it, turn on your macro recorder before you do it and it will give you the code. — braX, Jan 24 '18 at 13:29
I have tried that. The problem is that the code is set up to add a new time stamp whenever new data is added to the neighbouring columns on the same row. The data is added sequentially, so the new stamp ends up including the date every time it is added. — pottolom, Jan 24 '18 at 13:30
Possible duplicate of [Print dates in proper format](https://stackoverflow.com/questions/25160717/print-dates-in-proper-format) — dadler, Jan 24 '18 at 13:35

score 2 · Accepted Answer · answered Jan 24 '18 at 13:31

2

you may set format by your macro.

Cells(target.Row, 1).NumberFormat = "hh:mm"

answered Jan 24 '18 at 13:31

MarcinSzaleniec

2,246
1
7
22

HH:MM time stamping in Excel worksheet

1 Answers1