This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
There is frequently a need to log the time or date of data entry. While this can be done manually, or by using circular references and worksheet functions, the best way of time or date stamping a change to a cell in the worksheet is usually to use a worksheet or workbook event macro.
Typing CTRL-; will enter the current date in a cell. Typing CTRL-SHIFT-; (or, also, CMD-; for Macs) will enter the current time (to the minute only).
You can use a circular reference to enter the time when a change is made in another cell, then maintain that time. Choose ( for Macs) and check the Iteration checkbox. Then, if your target cell is A1 and you want the date/time to appear in B1, enter this in B1:
=IF(A1="","",IF(B1="",NOW(),B1))
Format B1 as you wish to display date, time, or both. If A1 is initially blank, B1 will return a null string (""). When a value is entered into A1, B1 will evaluate as "", therefore NOW() will be returned. After that (as long as A1 remains populated), B1 will evaluate to a date/time and therefore will return the value in B1 - i.e., the date/time.
Let's say that every time an entry is made in cells A2:A10, the corresponding cell in column B should have the date and time entered. You could use this Worksheet_Change() macro - put it in the worksheet code module:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
Note that this will clear the date-time when the target cell is cleared. Remove the "If IsEmpty(...)...End If" control structure if that's not desired (see alternate 1).
You can, of course use any valid date/time format.
If only the date is desired, instead of
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
use (see alternate 2)
.NumberFormat = "dd mmm yyyy"
.Value = Date
Similarly, if only the time is desired, use (see alternate 3)
.NumberFormat = "hh:mm:ss"
.Value = Time
This macro provides a lot of flexibility in inserting a date or time stamp. You can attach it to a toolbar button to stamp the active cell, or call it from an event macro.
Public Sub DateTimeStamp(ByVal ChangedCells As Range, _
Optional ByVal IncludeDate As Boolean = True, _
Optional ByVal IncludeTime As Boolean = True, _
Optional ByVal DTFormat As String = "dd mmm yyyy hh:mm", _
Optional ByVal RowOffset As Long = 0&, _
Optional ByVal ColOffset As Long = 1&, _
Optional ByVal ClearWhenEmpty As Boolean = True)
Const n1904 As Long = 1462
Dim bClear As Boolean
Dim rArea As Range
Dim rCell As Range
Application.EnableEvents = False
For Each rArea In ChangedCells.Areas
For Each rCell In rArea
With rCell
bClear = ClearWhenEmpty And IsEmpty(.Value)
With .Offset(RowOffset, ColOffset)
If bClear Then
.ClearContents
Else
.NumberFormat = DTFormat
.Value = Date * -IncludeDate - _
Time * IncludeTime + _
n1904 * .Parent.Parent.Date1904
End If
End With
End With
Next rCell
Next rArea
Application.EnableEvents = True
End Sub
The procedure should be self-explanatory. All arguments are optional except for the cells to monitor. By default, both the date and the time are entered in the cell to the right of the monitored cells.
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
Chip Pearson's page on event macros
Unfamiliar with macros? Check out David McRitchie's Getting Started with Macros and User Defined Functions