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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,184
Messages
5,857,822
Members
431,900
Latest member
Paradime0346

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