Date, Time and Now

Three functions to start with

• Date — today, with no time part.
• Time — the current time, with no date part.
• Now — both together, to the second.

Sub Stamp()
    Dim d As Date

    d = Now

    Debug.Print Date      ' 13.09.2026
    Debug.Print Time      ' 14:07:32
    Debug.Print Now       ' 13.09.2026 14:07:32
    Debug.Print d + 30    ' 13.10.2026
End Sub

Declare dates as Date

Use Dim d As Date, never String. A real Date can be compared with < and >, sorted, and shifted by whole days with plain arithmetic: d + 30 is thirty days later, and endDate - startDate is the number of days between them. Once a date has become text you lose all of that.

A macro you can run right now

This one appends a timestamped line to a log sheet every time it runs, creating the sheet on the first run. Paste it into a standard module — Alt+F11, Insert → Module — and press F5 two or three times.

Option Explicit

Sub StampRunLog()
    Dim ws As Worksheet
    Dim stampedAt As Date
    Dim startedAt As Double
    Dim projectStart As Date
    Dim daysRunning As Long
    Dim nextRow As Long

    startedAt = Timer
    stampedAt = Now
    projectStart = DateSerial(2026, 1, 1)

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Run Log")
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = "Run Log"
        ws.Range("A1:F1").Value = Array("Day", "Clock", "Full stamp", _
            "Weekday", "Days into project", "Seconds")
        ws.Range("A1:F1").Font.Bold = True
    End If

    daysRunning = DateValue(stampedAt) - projectStart
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    ws.Cells(nextRow, 1).Value = DateValue(stampedAt)
    ws.Cells(nextRow, 2).Value = TimeValue(stampedAt)
    ws.Cells(nextRow, 3).Value = stampedAt
    ws.Cells(nextRow, 4).Value = Format(stampedAt, "dddd")
    ws.Cells(nextRow, 5).Value = daysRunning
    ws.Cells(nextRow, 6).Value = Round(Timer - startedAt, 3)

    ws.Range("A" & nextRow).NumberFormat = "yyyy-mm-dd"
    ws.Range("B" & nextRow).NumberFormat = "hh:mm:ss"
    ws.Range("C" & nextRow).NumberFormat = "yyyy-mm-dd hh:mm:ss"
    ws.Columns("A:F").AutoFit
    ws.Activate
End Sub

Line by line

• startedAt = Timer gives the seconds since midnight as a Double. It is a stopwatch, not a clock: fine for measuring how long something took, useless across midnight, where it drops back to zero.
• stampedAt = Now reads the clock once. Every further mention of Now would ask Windows again, so two calls a few lines apart can disagree — and shortly before midnight they disagree about the day. One read, used six times, keeps the row consistent.
• projectStart = DateSerial(2026, 1, 1) builds a date from three numbers. Do not write #1/1/2026# instead: a VBA date literal is always read as month/day/year whatever your regional settings say, so #3/4/2026# is 4 March and never 3 April.
• The On Error Resume Next pair asks whether the sheet exists without stopping when it does not; the If ws Is Nothing block creates it and writes headers on the first run only.
• DateValue(stampedAt) keeps the day and drops the clock, TimeValue(stampedAt) does the opposite. Both hand back a genuine Date, so those columns stay sortable and filterable as dates.
• daysRunning = DateValue(stampedAt) - projectStart — subtracting two dates gives a plain count of days, so it belongs in a Long, not in a Date.
• Format(stampedAt, "dddd") returns the weekday name in the Windows display language, not in the language of your code.
• End(xlUp).Row + 1 finds the first free row, so each run appends instead of overwriting.
• The three NumberFormat lines change only what you see. Note that a cell format writes minutes as mm after hh, while VBA's Format function wants nn there — two different worlds, and lesson 5 deals with the second one.

The trap: the date that is really text

The most expensive habit with dates in VBA is writing the pretty version into the cell. It looks perfect and it is a string. Text sorts alphabetically, so 01.12.2026 lands before 02.01.2026; the filter offers a flat list instead of a year-month tree; SUMIFS with a date criterion quietly returns zero; and the next macro that subtracts two of them stops with a type mismatch.

' Wrong: the cell now holds text that merely looks like a date
ws.Range("A1").Value = Format(Now, "dd.mm.yyyy")

' Right: store the date, then decide separately how it is displayed
ws.Range("A1").Value = Now
ws.Range("A1").NumberFormat = "dd.mm.yyyy"

The rule is one line long: Format is for what a human reads — a message box, a file name, a sheet title. For a cell, put the Date in and let NumberFormat do the presenting. Not sure which one a cell already holds? Debug.Print TypeName(ws.Range("A1").Value) answers Date or String, and settles the argument in a second.

How this course goes on

Once dates behave as values instead of text, the rest is arithmetic you can trust. Lesson 2 shows the number hiding under every date and why the time part is a fraction of it, lesson 3 builds dates from parts with DateSerial, and lesson 4 shifts and measures them with DateAdd and DateDiff, including why adding a month is harder than adding thirty days. Lesson 6 takes on the dates that arrive as text from somebody else's system, and lesson 7 covers month ends, quarters and working days.