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) use your computer's locale and may vary slightly. CSV files and text-formatted cells are always read exactly as-is. If you need exact control, use Excel's =TEXT() function.
Contents
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, SecureMailMerge uses your computer’s locale settings to format the date. This means:
- If your computer is set to English (United Kingdom), you’ll get formats like 13 March 2026
- If your computer is set to English (United States), you’ll get formats like March 13, 2026
The result should closely match what you see in Excel, as long as the locale settings on your computer match across Excel and Outlook. If they differ, the output may vary slightly.
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.
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 exactly as it appears in the file. What you see in the CSV is what you get in your email.
Text-formatted cells
Similarly, if a cell in Excel is formatted as Text (not Date), SecureMailMerge reads it exactly as entered. The value in the email will match the cell content character-for-character. However, Excel will not treat it as a date, so features like sorting by date will not work in your spreadsheet.