Hi John,
You can manually time-stamp a cell by holding the Control and Shift keys down and hitting the colon key (CTRL Shift :). You can manually date stamp a cell by holding the Control key down and hitting the semicolon ; (CTRL ;). The results will be formatted using the default short time and date format for your locale.
If you want the time-stamping to be automatic, by far the best approach uses VBA code. Here is a macro that must be installed in the code pane for the worksheet being watched. Since a lot of people want to time stamp more than one cell, I generalized the code to put the time stamp in column B next to the cell in column A that was changed. If column A is blanked, then so too is column B. The code is restricted to watching cells A1:A10, but that is easily changed in the place indicated. Note that you'll need to format the cells that will be time-stamped to display the time/date as you wish.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range, cel As Range
Set targ = Intersect([A1:A10], Target) 'Change the reference to A1:A10 to the actual range you want to watch for changes
If targ Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cel In targ.Cells
If cel = "" Then
cel.Offset(0, 1).ClearContents
Else
cel.Offset(0, 1) = Now()
End If
Next
Application.EnableEvents = True
End Sub
Another approach puts the time-stamp in your cell when you double-click it. This sub also goes in the worksheet code pane.
Private Sub Worksheet_BeforeDoubleClic
Dim targ As Range, cel As Range
Set targ = Intersect([B1:B10], Target) 'Change the reference to B1:B10 to the actual range you want to time-stamp
If targ Is Nothing Then Exit Sub
For Each cel In targ.Cells
cel = Now()
Next
Cancel = True
End Sub
To install a sub in the code pane for a worksheet:
1) Right-click the sheet tab for the worksheet
2) Choose View Code from the resulting pop-up
3) Paste the suggested code in the resulting module sheet
4) ALT + F11 to return to the worksheet
If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.
Hoping to be helpful,
Brad
Main Topics
Browse All Topics
by: patrickabPosted on 2006-05-27 at 01:54:26ID: 16774873
John,
Here's a file illustrating how it can be done:
http://my.storenow.net?f=6
The macro is:
Sub timestamp2()
Selection = Now()
Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
End Sub
To see the macro press ALT+F11 and look in Module1 - ALT+F11 to get back to the worksheet.
Hope that helps
Patrick