Day 8 — Numbers, rounding and dates

Two things break reports more than anything else: a rounding rule nobody checked, and a date that is really text.

Chopping the decimals off

Function	Does	On 7.8	On -7.8
`Int`	Down, always	7	-8
`Fix`	Towards zero	7	-7
`Round`	To nearest	8	-8

Sub Chopping()
    Debug.Print Int(7.8), Fix(7.8), Round(7.8)
    Debug.Print Int(-7.8), Fix(-7.8), Round(-7.8)
    Debug.Print Abs(-42), Sgn(-42), Sqr(144)
End Sub

The rounding trap

VBA rounds an exact half to the nearest even number. This is deliberate - it is called banker’s rounding, and over thousands of rows it stops a persistent upward drift.

Sub RoundingTrap()
    Debug.Print "VBA Round"
    Debug.Print Round(0.5), Round(1.5), Round(2.5), Round(3.5)
    Debug.Print "Excel ROUND"
    Debug.Print WorksheetFunction.Round(0.5, 0), WorksheetFunction.Round(1.5, 0), _
                WorksheetFunction.Round(2.5, 0), WorksheetFunction.Round(3.5, 0)
End Sub

Read that output twice VBA gives 0, 2, 2, 4. Excel gives 1, 2, 3, 4. They disagree on every exact half. If your macro-produced total differs from the sheet total by a few rupees, this is very often the reason.
When you need the sheet’s behaviour, ask for it by name: WorksheetFunction.Round(x, 2).

Dates are numbers

Excel stores a date as a number of days counted from 30 December 1899. That is why you can subtract two dates and get days out.

Sub DateParts()
    Dim d As Date
    d = Cells(2, 2).Value

    Debug.Print d
    Debug.Print Year(d), Month(d), Day(d)
    Debug.Print MonthName(Month(d))
    Debug.Print Weekday(d)
End Sub

Date arithmetic

Sub DateMaths()
    Dim first As Date
    Dim last As Date

    first = Cells(2, 2).Value
    last = Cells(17, 2).Value

    Debug.Print "Days between:", DateDiff("d", first, last)
    Debug.Print "Months between:", DateDiff("m", first, last)
    Debug.Print "Thirty days on:", DateAdd("d", 30, first)
    Debug.Print "Same day next year:", DateAdd("yyyy", 1, first)
    Debug.Print "Built by hand:", DateSerial(2025, 12, 31)
End Sub

DateDiff, not subtraction last - first gives you days, which is often enough. DateDiff also does months, quarters and years, and it reads better: DateDiff("m", a, b) says what it means.

Format - turning values into text that looks right

Sub Formatting()
    Dim d As Date
    d = Cells(2, 2).Value

    Debug.Print Format(d, "yyyy-mm-dd")
    Debug.Print Format(d, "dd/mm/yyyy")
    Debug.Print Format(d, "mmm")
    Debug.Print Format(d, "mmmm")
    Debug.Print Format(d, "dddd")
    Debug.Print Format(0.256, "0.0%")
    Debug.Print Format(1234.5678, "0.00")
End Sub

Format gives you text, not a number Format(1234.5, "0.00") returns the string "1234.50". You cannot add it up any more. Use Format for labels and headings; to make a cell look right while staying a number, set .NumberFormat instead - that is tomorrow-but-one, on Day 11.
Number grouping follows your Windows region setting, so an India-English machine groups as 12,34,567 where a US one gives 1,234,567.

A monthly summary

Everything so far, doing a real job: group the orders by month.

Sub MonthlySummary()
    Dim i As Long
    Dim lastRow As Long
    Dim m As Long
    Dim janTotal As Double
    Dim febTotal As Double

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        m = Month(Cells(i, 2).Value)
        If m = 1 Then
            janTotal = janTotal + Cells(i, 6).Value * Cells(i, 7).Value
        ElseIf m = 2 Then
            febTotal = febTotal + Cells(i, 6).Value * Cells(i, 7).Value
        End If
        Cells(i, 9).Value = Format(Cells(i, 2).Value, "mmm")
    Next i

    Range("I1").Value = "Month"
    Debug.Print "January:", janTotal
    Debug.Print "February:", febTotal
End Sub

Try these yourself

Print Int, Fix and Round of -3.5 and explain each answer.
Show the difference between Round(4.5) and WorksheetFunction.Round(4.5, 0).
Print how many days passed between the row 2 and row 10 order dates.
Write the year of each order into column J using a loop.
Explain why Format returns text, and when that would break a total.

← PreviousDay 7 — Text functions - cleaning messy data Next →Day 9 — Subs, Functions and arguments