VBA Format in Excel — Dates, Numbers & Why Formatting Breaks Your Math

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-10

TL;DR — Format takes a value and a format code and hands you back a string that looks the way you asked. It is brilliant for building filenames, messages, and report labels — and it is a quiet bug factory when you write its result into a data cell, because that cell now holds text, not a number or date.

Sub FormatDemo()
    Debug.Print Format(1234.5, "#,##0.00")     ' 1,234.50
    Debug.Print Format(0.75, "0.0%")           ' 75.0%
    Debug.Print Format(Now, "yyyy-mm-dd")      ' 2026-06-10
    Debug.Print Format(Now, "dddd, d mmmm")    ' Wednesday, 10 June
    Debug.Print Format(38740, "$#,##0")        ' $38,740
End Sub

The signature, with the two optional arguments most people forget:

Format(expression, [format], [firstDayOfWeek], [firstWeekOfYear])
'       the value    code     for weekday math, rarely needed

The mental model: Format is makeup, not surgery

Format changes how a value looks, never what it is. Hand it the date serial 46183 and the code "yyyy-mm-dd", and you get the string "2026-06-10". The underlying value didn't change type on the way in — but what comes out is always a String. That is the single most important sentence about Format: the output is text.

Hold that and the whole tool falls into place. Use Format when the destination is a string — a message box, a sheet name, a "report_" & Format(Date, "yyyymmdd") & ".xlsx" filename, a label you're concatenating. Do not use it when the destination is a cell that needs to stay a number or a date, because you'll be pouring text into a slot that the rest of your workbook expects to do math on.

The one rule: Format returns a String — so it breaks the math downstream

This is the rule that explains the most common Format disaster:

The moment you write cell.Value = Format(someDate, "yyyy-mm-dd"), that cell stops being a date and becomes text that looks like a date. It won't sort chronologically, it won't filter by month, and =SUM over a column of "formatted numbers" returns 0.

Here is the trap, fully assembled:

' ⚠ WRONG — fills the column with text that only looks numeric
Range("C2").Value = Format(1234.5, "#,##0.00")   ' the cell now holds the STRING "1,234.50"
' =SUM(C:C) ignores it. Sorting treats "1,234.50" as text. Charts skip it.

If what you actually want is for the cell to display with thousands separators while staying a real number, you don't use Format at all — you set the cell's number format and leave the value alone:

' ✓ RIGHT — value stays numeric, only the display changes
Range("C2").Value = 1234.5
Range("C2").NumberFormat = "#,##0.00"            ' SUM, sort, charts all still work

That distinction — Format produces text, NumberFormat changes display — is the whole game. Burn it in: Format for strings, NumberFormat for cells.

The second trap: m means month, until it doesn't

Date and time codes share the letter m, and that ambiguity bites everyone at least once:

• A standalone "mm" is months: Format(Now, "mm") → 06.
• The same "mm" immediately after "hh:" is read as minutes: Format(Now, "hh:mm") → 09:05. Adjacency to the hour flips its meaning.
• For an elapsed time with no hour next to it, "mm:ss" gives you month:second — almost never what you meant. Use VBA's explicit minute token n: Format(t, "nn:ss").

Debug.Print Format(Now, "yyyy-mm-dd hh:mm:ss")   ' 2026-06-10 09:05:30  (first mm = month, second = minutes)
Debug.Print Format(Now, "mm:ss")                 ' 06:30  <- MONTH:second, the classic surprise
Debug.Print Format(Now, "nn:ss")                 ' 05:30  <- minutes, the fix

The placeholder digits have their own rule worth knowing: 0 forces a digit (padding with zeros), # shows a digit only if present. Format(5, "00") → 05; Format(5, "##") → 5. That's how you zero-pad invoice numbers or keep a clean integer.

Named formats and the Format* siblings

You don't always need a custom code. VBA ships named formats and locale-aware sibling functions:

Debug.Print Format(1234.5, "Currency")      ' $1,234.50   (uses the machine's regional currency)
Debug.Print Format(Now, "Short Date")       ' 6/10/2026   (regional order)
Debug.Print Format(0.75, "Percent")         ' 75.00%

Debug.Print FormatCurrency(1234.5, 2)       ' $1,234.50
Debug.Print FormatNumber(1234.5, 2)         ' 1,234.50
Debug.Print FormatPercent(0.75, 1)          ' 75.0%
Debug.Print FormatDateTime(Now, vbLongDate) ' Wednesday, June 10, 2026

The named formats and Format* siblings respect the machine's regional settings, which is a feature for user-facing display and a hazard for data you intend to round-trip — "Short Date" is 6/10/2026 in the US and 10/06/2026 in most of Europe. When you need a fixed, machine-independent string (a filename, a CSV key, an ISO timestamp), spell out the code explicitly — "yyyy-mm-dd" — and never "Short Date".

When to use which

The opinion: if it lands in a cell, it's NumberFormat, not Format

Here's the line I'll defend: Format should almost never write into a data cell. Ninety percent of the "my SUM is zero" and "my dates won't sort" tickets trace back to someone using Format where they wanted NumberFormat. The two read almost the same in code, which is exactly why the bug is so common and so invisible in review.

So make it a habit with a clear split. Building a string — a sheet tab name, a log line, a filename stamp, text you're concatenating into a MsgBox? Format. Making a cell look right while keeping it a number or date the workbook can compute on? NumberFormat, value untouched. Get that split right and an entire class of silent data bugs disappears. And remember the one-way street: Format only goes value → text. To go the other way — text that should be a real number or date — you need CStr, CDate and Val, not Format.

Common Format mistakes (and the fix)

When the formatting is half the macro — describe the result instead

Formatting is never the goal; the report is. You wanted "a monthly sheet where amounts show as euros, dates as ISO, and the totals still add up" — and instead you're three layers deep deciding whether this mm is a month, whether this column is text or number, and why the chart skipped a row. ExcelMaster Agent lets you state the result in plain English — "format column C as currency, column A as yyyy-mm-dd, keep them numeric so the totals work" — and it writes Python that sets number formats instead of stringifying your data, backing up the file first. Try it free →

Related guides

• VBA Trim — Why It Doesn't Remove Your Spaces (and the Chr(160) Fix)
• VBA CStr, CDate & Val — Convert Text to Numbers & Dates the Right Way
• VBA InStr — Find Text Inside a String
• VBA Split — Turn One String Into an Array
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

What does the Format function do in VBA? Format converts a value into a formatted string using a format code — Format(1234.5, "#,##0.00") returns the text "1,234.50". It changes how the value looks, not what it is, and the result is always text.

How do I format a date in VBA? Use an explicit code: Format(Now, "yyyy-mm-dd") gives 2026-06-10; Format(Now, "dd/mm/yyyy") gives day-first. Avoid the named "Short Date" if the string must be the same on every machine, because it follows each PC's regional settings.

Why does my VBA Format show the month instead of the minutes? Because m means month by default. A standalone "mm" is the month; it only means minutes when it sits right after "hh:". For an elapsed time, use VBA's explicit minute token: Format(t, "nn:ss").

Why does SUM return 0 after I format a column with VBA? You almost certainly wrote Format() results into the cells, which stores text that merely looks numeric. Store the real number and set the appearance with Range.NumberFormat = "#,##0.00" instead — then SUM, sort, and charts work again.

What is the difference between Format and NumberFormat in VBA? Format returns a text string; Range.NumberFormat changes how a cell displays while the stored value stays a real number or date. Use Format to build strings, and NumberFormat to make cells look right without breaking calculations.