Wauvel

Excel like a finance pro.

← All VBA snippets

Do date arithmetic in VBA

Month-ends, quarter starts and ages in code — and the locale trap when a date is written back.

1What it does

VBA stores dates as numbers the same way Excel does, so arithmetic works: add 30 to a date and you have a date 30 days later. The functions that make it readable are `DateSerial` (build a date from year, month and day), `DateAdd` (move by months or quarters) and `DateDiff` (measure between two). VBA has no EOMONTH, but DateSerial gives you the month-end trick for free: day zero of next month is the last day of this one, so every month-end lands correctly including February. The trap is writing a date into a cell as TEXT, which Excel then re-reads in the machine's locale order.

2The code

Sub PeriodDates()
    Dim asOf As Date
    asOf = DateSerial(2026, 8, 17)

    ' Month-end: day ZERO of next month is the last day of this one.
    ' Handles February and 30-day months with no special cases.
    Dim monthEnd As Date
    monthEnd = DateSerial(Year(asOf), Month(asOf) + 1, 0)       ' 31 Aug 2026

    ' Start of the quarter the date falls in.
    Dim qStart As Date
    qStart = DateSerial(Year(asOf), ((Month(asOf) - 1) \ 3) * 3 + 1, 1)   ' 1 Jul 2026

    ' Move by months — DateAdd clamps to month-end, so 31 Jan + 1 month = 28/29 Feb.
    Dim nextMonth As Date
    nextMonth = DateAdd("m", 1, asOf)

    ' Whole months between two dates.
    Dim monthsOld As Long
    monthsOld = DateDiff("m", DateSerial(2026, 3, 31), asOf)

    With ActiveSheet
        ' Write the DATE value, not Format(...) text, or the locale reorders it.
        .Range("B2").Value = monthEnd
        .Range("B3").Value = qStart
        .Range("B4").Value = nextMonth
        .Range("B5").Value = monthsOld
        .Range("B2:B4").NumberFormat = "d mmm yyyy"
    End With
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Compute month-ends and quarter starts for a reporting macro.
  • Roll a period forward by a month or a year inside a loop.
  • Age invoices in whole months without worksheet formulas.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

asOf = DateSerial(2026, 2, 17)
Month-end: DateSerial(y, m + 1, 0)28 Feb 2026
Quarter start1 Jan 2026
DateAdd("m", 1, asOf)17 Mar 2026

Day ZERO of next month is the last day of this one. Try 31 January and a leap-year February.

5Pitfalls

A date written as Format(d, "dd/mm/yyyy") text is re-read by Excel in US order, swapping day and month for days 1-12.

Fix: Write the Date value itself and apply a NumberFormat to the cell, as above.

DateDiff("m") counts month BOUNDARIES crossed, so 31 Jan to 1 Feb is one month.

Fix: That's by design. For complete months, compare day numbers too, or use DATEDIF on the sheet.

A literal like #08/17/2026# is always read month-first regardless of locale, which surprises non-US readers of the code.

Fix: Use DateSerial(2026, 8, 17) — unambiguous to anyone reading it.

The quarter formula uses / instead of \ and produces a fraction.

Fix: Integer division in VBA is the backslash: (Month - 1) \ 3.

6No-code alternatives

  • EOMONTH and EDATE on the sheet — The formula equivalents, which recalculate and are easier to audit.
  • WorksheetFunction.EoMonth — Calls the sheet function from VBA when you'd rather not use the DateSerial trick.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.