# Mail Merge Date Format: Fix Dates, Numbers and Currency

> Mail merge date format wrong? Dates as serial numbers, 15 decimals, lost £ or $? Fix it with Word field switches, Excel TEXT() or SecureMailMerge.

A mail merge date format goes wrong (46094 instead of 13 March 2026), and £1,250.50 merges as 1250.5, because Excel stores the raw value and keeps the formatting in the cell. Fix it before 300 invoice reminders go out saying the balance is 1250.5 and due on 46094: in Word with field switches (`\@` for dates, `\#` for numbers), in Excel with a `TEXT()` column that works in any tool, or in Outlook with SecureMailMerge, which reads the cell format as shown.

## Why merged emails show the wrong date or number

In Excel, 13 March 2026 is stored as the number 46094 (days counted from the start of 1900), and £1,250.50 is stored as 1250.5. What you see in the cell is that value passed through a number format such as `d mmmm yyyy` or `"£"#,##0.00`.

Word's mail merge connects to the workbook and reads the stored value, not the formatted display. Microsoft documents this for [dates, phone numbers and currency](https://support.microsoft.com/en-us/help/304387) and lists the same symptoms in its [troubleshooting article on lost Excel formatting](https://learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/excel/excel-data-not-retain-formatting-mail-merge):

| In Excel | In the Word merge field | What happened |
| --- | --- | --- |
| 50% | .5 | The percentage format was dropped |
| $12.50 | 12.5 | Currency symbol and trailing zero lost |
| 07865 | 7865 | The leading zero of a ZIP code vanished |
| 13/03/2026 | 3/13/2026 or 46094 | Date shown in US order, or as a serial number if Word reads the column as numbers |

You may also see long decimal tails (such as `0.30000000000000004`) on values calculated by formulas, because Word shows the full stored number rather than the rounded one Excel displays.

There are three routes: [Word field switches](#fix-1-word-mail-merge-date-format-and-number-format-switches) when you merge in Word, an [Excel TEXT() column](#fix-2-format-in-excel-with-text-works-everywhere) that works in any tool, or [SecureMailMerge](#fix-3-merge-from-outlook-with-securemailmerge-no-switches-or-helper-columns), which reads the cell format with no setup.

  Edit the field code. Word only.
  Finished text in a helper column. Works in any tool.
  Reads the cell format as shown. No setup.

## Fix 1: Word mail merge date format and number format switches

A Word merge field accepts a formatting switch, a small pattern written inside the field code. `\@` sets the mail merge date format and `\#` sets the mail merge number format. You add switches by editing the field code directly.

### Step 1: Show the field codes

Open your merge document and press **Alt+F9**. Every merge field changes from `«PaymentDate»` to its underlying code, for example `{ MERGEFIELD PaymentDate }`.

### Step 2: Type the switch inside the braces

Click just before the closing brace and type the switch, for example `\@ "d MMMM yyyy"` for a date or `\# "£,0.00"` for an amount. Keep the quotation marks around patterns that contain spaces or symbols.

### Step 3: Update the field and switch back

Press **F9** with the cursor in the field to update it, then press **Alt+F9** again to return to the normal view.

### Step 4: Preview the results

On the **Mailings** tab, choose **Preview Results** and step through several records, including one with a negative amount, an empty cell and a single-digit day, before you finish the merge.

### Date switch `\@`

The date switch is case-sensitive: `M` is month and `m` is minutes, so `dd/mm/yyyy` will print minutes where you expected the month.

In a Word date switch, capital M is month and lowercase m is minutes. In Excel TEXT(), lowercase m works for both.

```
{ MERGEFIELD PaymentDate \@ "d MMMM yyyy" }      13 March 2026
{ MERGEFIELD PaymentDate \@ "dd/MM/yyyy" }       13/03/2026
{ MERGEFIELD PaymentDate \@ "MMMM d, yyyy" }     March 13, 2026
{ MERGEFIELD PaymentDate \@ "dddd, d MMMM" }     Friday, 13 March
{ MERGEFIELD PaymentDate \@ "yyyy-MM-dd" }       2026-03-13
```

### Mail merge currency format and number switch `\#`

In a numeric picture, `0` always prints a digit (padding with zeros), `#` prints a digit only where there is one, and a comma adds a thousands separator. Currency symbols and `%` print as typed, and a semicolon separates the positive and negative formats.

The widely copied pattern `\# "$#,##0.00"` is Microsoft's own example and works, but [Microsoft's Formula field reference](https://support.microsoft.com/en-us/office/field-codes-formula-field-32d5c9de-3516-4ec3-80ed-d1fc2b5bc21d) notes that each `#` with no digit to show prints a space. Dropping the `#` placeholders, as in `\# "$,0.00"`, gives the same result without that risk, so the examples below use the shorter form.

```
{ MERGEFIELD Amount \# "0.00" }                  1250.50
{ MERGEFIELD Amount \# ",0.00" }                 1,250.50
{ MERGEFIELD Amount \# "$,0.00" }                $1,250.50
{ MERGEFIELD Amount \# "$#,##0.00" }             $1,250.50 (Microsoft's form)
{ MERGEFIELD Amount \# ",0.00 €" }               1,250.50 €
{ MERGEFIELD Amount \# "£,0.00;(£,0.00)" }       (£1,250.50) for -1250.5
```

For mail merge decimal places, the number of zeros after the point sets them: `"0.00"` gives two decimals and rounds the rest.

### Mail merge percentage format

The `\#` switch formats a number but cannot multiply it, so a cell showing 12.5% still merges as .125. Wrap the merge field in a formula field and multiply by 100. Insert each pair of field braces with **Ctrl+F9**; typed braces do not work.

  .125
  12.5
  12.5%

```
{ = { MERGEFIELD Rate } * 100 \# "0.0%" }        12.5%
```

### Mail merge leading zeros, ZIP codes and IDs

A mail merge ZIP code that arrives as 2134 instead of 02134 is the classic case. ZIP codes, postcodes, account numbers and phone numbers are identifiers, not numbers, so the cleanest fix is to store them as text: set the column's format to **Text** in Excel before you type or paste the values, because Excel drops a leading zero the moment it stores the entry as a number.

  02134 becomes 2134
  02134 stays 02134

If the column is already numeric, a `0` placeholder pads the value back to a fixed length:

```
{ MERGEFIELD Zip \# "00000" }                    02134
```

### The DDE alternative

Word can also read values exactly as Excel displays them through a legacy DDE connection: enable **Confirm file format conversion on open** under **File > Options > Advanced**, then pick **MS Excel Worksheets via DDE** when the wizard asks for the data source. [Microsoft documents the steps](https://learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/excel/excel-data-not-retain-formatting-mail-merge); test on a copy first.

## Fix 2: Format in Excel with TEXT() (works everywhere)

The most portable fix is to do the formatting in Excel. The `TEXT()` function turns a value into finished text, so every mail merge tool, including Word, Outlook add-ins and CSV exports, receives exactly what you see. Keep the original column for calculations and add a display column next to it. For example a row stores 46094 and 1250.5 while the display columns beside them read 13 March 2026 and £1,250.50, and 46101 shows as 20 March 2026; a negative amount such as -80 can show as (£80.00).

| Need | Formula | Result |
| --- | --- | --- |
| Long date | `=TEXT(A2,"d mmmm yyyy")` | 13 March 2026 |
| UK short date | `=TEXT(A2,"dd/mm/yyyy")` | 13/03/2026 |
| US date | `=TEXT(A2,"mm/dd/yyyy")` | 03/13/2026 |
| Two decimal places | `=TEXT(B2,"0.00")` | 1250.50 |
| Currency | `=TEXT(B2,"£#,##0.00")` | £1,250.50 |
| Percentage | `=TEXT(C2,"0.0%")` | 12.5% |
| Leading zeros | `=TEXT(D2,"00000")` | 02134 |

Unlike Word, Excel's `TEXT()` in English-language Excel uses `m` for both months and minutes and works out which one you mean from context, so `dd/mm/yyyy` is correct here. [Microsoft's TEXT function reference](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c) lists every format code, and our help page shows a [step-by-step TEXT() date example](https://www.securemailmerge.com/help/date-formatting-in-mail-merge/).

> **CSV files carry no formatting**
>
> A CSV file stores plain text. When you save a workbook as CSV, Excel generally writes each value as it is displayed, so check the CSV in a text editor before you merge. If you open that CSV in Excel again, Excel may convert the values back to numbers and strip leading zeros.

## Fix 3: Merge from Outlook with SecureMailMerge, no switches or helper columns

Fix 1 only works when you merge in Word, and Fix 2 means adding a helper column for every formatted column. If you send from Outlook, [SecureMailMerge](https://www.securemailmerge.com/store) reads the text each cell format shows, so the spreadsheet needs no changes. It is a mail merge add-in that runs inside Outlook. Instead of the raw stored value, it reads the text produced by each cell's number format, so most spreadsheets merge the way they look in Excel with no switches or helper columns.

| Excel cell value | Cell format | Merged text |
| --- | --- | --- |
| 1250.5 | `"£"#,##0.00` | £1,250.50 |
| 1250.5 | `#,##0.00 "€"` | 1,250.50 € |
| 0.125 | `0.0%` | 12.5% |
| 2134 | `00000` | 02134 |
| 46094 | `d mmmm yyyy` | 13 March 2026 |

A few things to know. The region-dependent long date (marked with `*` in Excel's Format Cells dialog) follows your computer's settings. For predictable results, choose a date format without the `*` marker, or use a `TEXT()` column. Very complex custom formats with conditional sections or colour codes may not match, so use a `TEXT()` column for those. CSV files and Text-formatted cells are read as written, except that numbers in scientific notation are expanded to full digits. One bonus: long numbers in General format, such as phone numbers, merge with their full digits rather than in scientific notation.

  Fixed and most custom formats: kept
  Region-dependent dates (*): pick a format without the * marker
  Complex custom formats: use a TEXT() column
  CSV and Text cells: read as written

Because values arrive as display text, numeric [conditions](https://www.securemailmerge.com/help/conditions/) such as `{% if OrderTotal >= 100 %}` need a column that shows a plain number, so keep an unformatted copy of any column you compare and use the formatted one in the email text. For empty cells, special characters in headers or files that will not load, see [default values](https://www.securemailmerge.com/help/specify-fallback-default-values-for-mail-merge/), [Liquid syntax errors](https://www.securemailmerge.com/help/error-liquid-syntax/) and [spreadsheet troubleshooting](https://www.securemailmerge.com/help/spreadsheet-troubleshooting/).

## Quick-reference table for Word switches and Excel TEXT()

| Problem | Word field switch | Excel TEXT() |
| --- | --- | --- |
| Date shows as 46094 or US order | `\@ "d MMMM yyyy"` | `=TEXT(A2,"d mmmm yyyy")` |
| Too many decimal places | `\# "0.00"` | `=TEXT(B2,"0.00")` |
| Currency symbol missing | `\# "£,0.00"` | `=TEXT(B2,"£#,##0.00")` |
| Percentage shows as .125 | `{ = { MERGEFIELD Rate } * 100 \# "0.0%" }` | `=TEXT(C2,"0.0%")` |
| ZIP code loses leading zero | `\# "00000"` | `=TEXT(D2,"00000")` |
| Negative amounts | `\# ",0.00;(,0.00)"` | `=TEXT(B2,"#,##0.00;(#,##0.00)")` |

The table covers Fix 1 and Fix 2 only. For SecureMailMerge, see Fix 3 above.

## Checklist for SecureMailMerge campaigns with statements, invoices and price lists

- Format every date, amount and percentage column in Excel the way it should read in the email.
- Store ZIP codes, account numbers and phone numbers as Text before entering them.
- Spell out the month (13 March 2026) when recipients live in different countries.
- Keep an unformatted copy of any column you use in a numeric condition, and add a fallback for columns that can be empty.

For the full workflow from spreadsheet to sent campaign, read the [complete guide to mail merge in Outlook](https://www.securemailmerge.com/mail-merge-outlook/) or the walkthrough on [how to mail merge from Excel](https://www.securemailmerge.com/knowledge/mail-merge-excel/).

## Frequently asked questions

### Why does mail merge show my date as a number like 46094?

Excel stores dates as serial numbers, and 46094 is 13 March 2026. Word reads that stored value. Add a date switch such as `\@ "d MMMM yyyy"` to the field, or merge from a `TEXT()` column.

### How do I keep two decimal places in a mail merge?

In Word, add `\# "0.00"` (or `\# ",0.00"` for a thousands separator) to the merge field. In Excel, use `=TEXT(B2,"0.00")`. SecureMailMerge keeps the decimals shown by the cell's number format.

### How do I show a percentage correctly in mail merge?

Word merges 12.5% as .125. Use a formula field, `{ = { MERGEFIELD Rate } * 100 \# "0.0%" }`, inserting braces with Ctrl+F9, or merge from `=TEXT(C2,"0.0%")`. SecureMailMerge keeps the cell's percentage format.

### Why do ZIP codes lose their leading zero?

Excel stores 02134 as the number 2134 unless the cell is formatted as Text. Format the column as Text before entering values, pad it in Word with `\# "00000"`, or use the `00000` number format.

### Does SecureMailMerge keep my Excel date format?

Yes, in most cases. It reads the formatted value Excel shows. Fixed and most custom formats are preserved. The region-dependent long date follows your computer's settings, so choose a format without the `*` marker or use a `TEXT()` column for a guaranteed result.

### Does the fix differ for CSV files?

Yes. A CSV contains plain text with no cell formatting, so values merge as written in the file, apart from numbers in scientific notation, which are expanded. Check the CSV in a text editor, and fix the formatting in Excel before exporting.