Home

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.

Alternate time and date stamps

Alternate 1 (stamp when cleared)

    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
                With .Offset(0, 1)
                    .NumberFormat = "dd mmm yyyy hh:mm:ss"
                    .Value = Now
                End With
                Application.EnableEvents = True
            End If
        End With
    End Sub

Alternate 2 (date only)

    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
                With .Offset(0, 1)
                    .NumberFormat = "dd mmm yyyy"
                    .Value = Date
                End With
                Application.EnableEvents = True
            End If
        End With
    End Sub

Alternate 3 (time only)

    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
                With .Offset(0, 1)
                    .NumberFormat = "hh:mm:ss"
                    .Value = Time
                End With
                Application.EnableEvents = True
            End If
        End With
    End Sub

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

Back to date and time stamps