Record Current Time of Day Multiple Times

circlek828

New Member
Joined
Oct 14, 2011
Messages
8
I am trying to find a way to record the current time of day in the HH:MM:SS format down to the second in Column F of my worksheet whenever I make an entry in Column A of the worksheet. I have tried using the Ctrl+Shift+; but it only gives me values for Hours and minutes not seconds. Is there a way I can do this in excel?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
Yes, that way only seems to enter time to the nearest minute.
Right-click the sheet's tab, choose View code, then paste this code where the flashing cursor is (assuming you don't already have a change event for that sheet):
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
TimeGrab = Now
Static IsBlocked As Boolean
If Not IsBlocked Then
  IsBlocked = True
  Set ChangingCells = Intersect(Target, Columns(1))
  If Not ChangingCells Is Nothing Then
    For Each cll In ChangingCells.Cells
      With Cells(cll.Row, "F")
        .Value = TimeGrab
        .NumberFormat = "h:mm:ss" '.NumberFormat = "dd/mm/yyyy hh:mm:ss"
      End With
    Next cll
  End If
  IsBlocked = False
End If
End Sub
Now go back to the sheet and test. If it's ok then close the newly opened application, save the Excel workbook and you're done.
This macro actually pastes the time and date, but formats the cell to show only time, so date information remains in the cell. If you want truly only to have time in the cell, change:
TimeGrab = Now
to
TimeGrab = Time
and you can also lose the line beginning .NumberFormat.

On the other hand, you may want to keep the date info and see it, so retain the:
TimeGrab = Now
line and use
.NumberFormat = "dd/mm/yyyy hh:mm:ss"
as the .NumberFormat line
although you might want to adjust dd/mm to mm/dd if you're in the US - very confusing - I tend to use a date format with mmm in so that the month appears as letters rather than a number.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,983
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top