How are dates formatted in a mail merge?
SecureMailMerge preserves most Excel date formats automatically. Fixed and custom formats work reliably. Region-dependent formats (marked with * in Excel) may not come out as you expect, so choose a format without the * marker for predictable results. CSV files and text-formatted cells are read as written, except that numbers in scientific notation are expanded. If you need exact control, use Excel's =TEXT() function.
Excel date formatting
Excel stores dates as numbers internally and uses format settings to control how they are displayed. When you format a date cell in Excel, you are choosing how the number is shown, the underlying value stays the same.
There are three types of date formats in Excel:
Region-dependent formats (marked with *)
In Excel’s Format Cells dialog, some date formats are prefixed with an asterisk (*), for example *14/03/2012 or *14 March 2012. These formats change depending on your computer’s regional settings. The same spreadsheet will show different date formats on computers set to different regions.
Fixed formats
Formats without an asterisk are fixed patterns that always look the same regardless of your region. Examples include 14/03/2012, 2012-03-14, and March 14, 2012. The exact pattern is saved in the spreadsheet file.
Custom formats
You can also create your own format in Excel’s Format Cells dialog, such as dd-MMM-yyyy to produce 13-Mar-2026 or dd MMMM yyyy to produce 13 March 2026.
SecureMailMerge date formatting
When SecureMailMerge reads your spreadsheet, it tries to preserve the formatted value you see in Excel. How well this works depends on the format type:
Region-dependent formats
Because these formats don’t contain a fixed pattern, the result can differ from what you see in Excel. The region-dependent long date (for example *14 March 2012) follows your computer’s settings.
For predictable results, choose a date format without the * marker, or use a =TEXT() column as described below.
Fixed formats
These are generally preserved accurately. Examples:
| Excel Format | Example Output |
|---|---|
14/03/2012 | 13/03/2026 |
14/03/12 | 13/03/26 |
2012-03-14 | 2026-03-13 |
14.3.12 | 13.3.26 |
March 14, 2012 | March 13, 2026 |
Custom formats
Most standard custom patterns are reproduced correctly:
| Custom Format | Example Output |
|---|---|
dd/mm/yyyy | 13/03/2026 |
dd-MMM-yyyy | 13-Mar-2026 |
yyyy-mm-dd | 2026-03-13 |
dd MMMM yyyy | 13 March 2026 |
mmm dd, yyyy | Mar 13, 2026 |
Very complex custom formats with conditional sections or colour codes may not appear identically.
Exact formatting with =TEXT()
If the automatic formatting isn’t producing the right result, you can use Excel’s =TEXT() function to convert the date into a fixed text value that SecureMailMerge will read exactly as displayed.
How to use it
- Keep your date value in one column (e.g. column A)
- In another column, use a
=TEXT()formula to format it exactly how you want - Use the new column as your merge field instead of the original date column
Formula:
=TEXT(A2, "format_code")Formula examples
| Formula | Output |
|---|---|
=TEXT(A2, "dd/mm/yyyy") | 13/03/2026 |
=TEXT(A2, "dd MMMM yyyy") | 13 March 2026 |
=TEXT(A2, "dddd, dd MMMM yyyy") | Friday, 13 March 2026 |
=TEXT(A2, "dd-MMM-yyyy") | 13-Mar-2026 |
=TEXT(A2, "yyyy-mm-dd") | 2026-03-13 |
=TEXT(A2, "mm/dd/yyyy") | 03/13/2026 |
Step-by-step example
Suppose column A has your dates and column B has email addresses:
| A (Date) | B (Email) | C (Formatted Date) | |
|---|---|---|---|
| 1 | Date | Display Date | |
| 2 | 13/03/2026 | [email protected] | =TEXT(A2, "d MMMM yyyy") |
| 3 | 14/03/2026 | [email protected] | =TEXT(A3, "d MMMM yyyy") |
Column C will show 13 March 2026, 14 March 2026, etc. Use {{Display Date}} as your merge token in the email instead of {{Date}}.
Tip: You can hide column A in Excel if you don’t want to see the raw date alongside the formatted version.
Numbers and currency
Number, currency and percentage cells work the same way as dates: SecureMailMerge uses the text produced by the cell’s number format, not the raw stored value. A cell formatted as "£"#,##0.00 merges as £1,250.50, a 0.0% cell as 12.5%, and a 00000 cell keeps its leading zeros as 02134. Long numbers in General format, such as phone numbers, merge with all their digits instead of in scientific notation. The =TEXT() function above works for numbers too, for example =TEXT(B2, "#,##0.00").
Because values arrive as formatted text, a condition that compares numbers needs a column that shows a plain number, such as 1250.5. For the Word and Excel fixes for dates, numbers and currency, see mail merge date, number and currency formatting.
Other file formats
CSV files
If you are using a CSV file instead of an Excel workbook, dates are not affected by any of the above. CSV files contain plain text, so SecureMailMerge reads each value as it appears in the file. The one exception: numbers written in scientific notation, such as 1.23E+5, are expanded to full digits (123000).
Text-formatted cells
Similarly, if a cell in Excel is formatted as Text (not Date), SecureMailMerge reads it as entered, with the same exception: numbers in scientific notation are expanded to full digits. However, Excel will not treat it as a date, so features like sorting by date will not work in your spreadsheet.
Related
- Set default values for empty fields, insert fallback text when a date (or any) cell is empty.
- Using conditions to show or hide content, show different text depending on a recipient’s data.
Need more help?
Email support with what you were trying to do, what happened and the exact error message. Do not include real recipient data.