General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Time and date stamps

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.

Manual entry

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).

Using circular references and worksheet functions

You can use a circular reference to enter the time when a change is made in another cell, then maintain that time. Choose Tools/Options/Calculation (Preferences/Calculation 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:


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.

Using a worksheet event macro.

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
                    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

DateTimeStamp Macro

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
                           .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.

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

Chip Pearson's page on event macros

Where to put your macros

Unfamiliar with macros? Check out David McRitchie's Getting Started with Macros and User Defined Functions