Mail Merge Fix • Dates and Currency

Fix: Excel Date and Currency Format in Word Mail Merge

Fix dates that appear as serial numbers and amounts that lose their currency format. Start with Word field switches, use Excel display columns when presentation rules are stricter, and validate difficult records before the full merge.

Word field switches
Excel display columns
Excel VBA helper
Six-record pilot
Keep source dates and amounts numeric. Control only the representation sent to Word, then preview the exact records that challenge blanks, negatives, zero, and regional separators.

Quick answer: choose where the format should be controlled

Word may read the stored Excel value instead of the cell’s visual format. Use one of these two fixes, then verify the final merged document rather than the spreadsheet preview alone.

Use a Word field switch

Best when the source contains valid numeric dates and amounts and one Word template owns the presentation. Add \@ for dates or \# for numbers inside the existing MERGEFIELD.

Use an Excel display column

Best when blank handling, literal currency text, locale rules, or downstream mapping must be explicit before Word connects to the workbook. Keep the original numeric columns beside the display text.

Use VBA only for repeat preparation

A macro is useful when refreshed workbooks repeatedly need the same helper columns. It should validate the source types, write only the designated output columns, save the workbook, and stop on the first invalid row.

Diagnose the symptom before changing the template

The same visible error can come from a missing Word switch, a text value in Excel, a stale connection, or a locale assumption.

Date appears as 45292

Word received an Excel date serial. Keep the source as a real date and add a \@ switch or merge a prepared date-text column.

Date includes 00:00:00

The connection exposed a native date-time value. Apply an explicit Word date picture so the output includes only the required date parts.

Amount loses $ or €

The symbol was cell formatting, not part of the stored number. Use a numeric picture switch or a display-text column that includes the approved symbol.

Separators look wrong

The Word field, Excel formula, and Windows region may use different conventions. Choose the required output locale and test on the machine that runs the merge.

Blank becomes zero

A numeric format may render an empty or missing value as zero. If blank and zero have different business meanings, build that rule into a display column.

Preview shows old data

The workbook may not have been saved or Word may still use an earlier connection state. Save, reconnect or refresh, and preview the affected records again.

Fix the existing Mail Merge fields in Word

Microsoft documents the date-time picture switch \@ and numeric picture switch \# for formatting field results. Press Alt+F9 to toggle field codes, edit the existing field, toggle back, and preview records.

Edit a real Word field

Do not type ordinary { and } characters around a new field. Insert the merge field through Word, then add the switch inside its existing field code.

Date with a written month

{ MERGEFIELD InvoiceDate \@ "MMMM d, yyyy" }

Example result: November 17, 2026.

Unambiguous numeric date

{ MERGEFIELD InvoiceDate \@ "yyyy-MM-dd" }

Example result: 2026-11-17.

Currency with three states

{ MERGEFIELD Amount \# "$#,##0.00;($#,##0.00);$0.00" }

Positive, negative, and zero values receive explicit formats.

Use the approved symbol

Replace the literal currency symbol and picture with the business-approved output. Do not assume the source workbook’s cell styling will carry through the connection.

References: Microsoft’s Mail Merge formatting workaround and Word field-result format switches.

Build document-ready display columns in Excel

Use this route when Word should receive the final text rather than interpret a raw number. Microsoft notes that TEXT converts a number to text, so keep the original date and amount columns for calculations.

Date display column

=IF(B2="","",TEXT(B2,"yyyy-mm-dd"))

Blank stays blank; a real Excel date becomes unambiguous text.

Currency display column

=IF(D2="","",TEXT(D2,"$#,##0.00;($#,##0.00);$0.00"))

The three sections define positive, negative, and zero output.

Keep raw columns numeric

Use InvoiceDate and Amount for sorting, formulas, and validation. Map InvoiceDateDisplay and AmountDisplay only to document fields.

Confirm locale behavior

Formula argument separators and displayed decimal or thousands separators can vary by regional settings. Test the workbook on the computer that prepares the merge.

References: Excel TEXT function and prepare an Excel source for Word Mail Merge.

Define the source contract before automating it

The VBA helper below uses header names rather than fixed column letters. The five headers must be unique in row 1, but their order can change.

Save as .xlsm

Save the workbook as an Excel Macro-Enabled Workbook before adding and running the helper.

Use a Data sheet

Create a worksheet named Data. Row 1 contains headers and data begins in row 2.

Provide five exact headers

RecordID, InvoiceDate, Amount, InvoiceDateDisplay, and AmountDisplay.

Keep output columns disposable

After validating all source rows, the macro overwrites the two display columns, saves the workbook, and leaves the source date and amount unchanged. A write or save failure may still leave unsaved changes in Excel; review or discard them before retrying.

Blank policy

A blank date or amount remains blank. Zero remains a formatted zero. Every row through the final RecordID must have an ID, so accidental holes stop the preparation run.

Free Excel VBA helper: prepare verified Mail Merge display text

This macro validates the workbook contract, rejects text-typed dates and amounts (including numeric-looking strings), checks every source row before writing output columns, saves the workbook, and tells the user to refresh Word before the merge.

Headers, not letters

Columns can move without silently redirecting the macro, provided each required header remains unique.

Numeric source types

Date and amount cells must contain numeric Excel values, not text that merely looks numeric. The code normalizes Excel’s 1904 date system before formatting.

Text output enforced

The two display columns use Text format before values are written, and each written value is read back for verification.

Application state restored

The original calculation mode is left unchanged. Screen updating, events, and the status bar are restored after success or failure.

Option Explicit

Private Const DATA_SHEET As String = "Data"
Private Const HEADER_ROW As Long = 1
Private Const FIRST_DATA_ROW As Long = 2

Private Const HEADER_RECORD_ID As String = "RecordID"
Private Const HEADER_DATE_SOURCE As String = "InvoiceDate"
Private Const HEADER_AMOUNT_SOURCE As String = "Amount"
Private Const HEADER_DATE_OUTPUT As String = "InvoiceDateDisplay"
Private Const HEADER_AMOUNT_OUTPUT As String = "AmountDisplay"

Private Const DATE_FORMAT_TEXT As String = "yyyy-mm-dd"
Private Const CURRENCY_SYMBOL As String = "$"
Private Const NUMBER_FORMAT_TEXT As String = "#,##0.00"

Public Sub BuildMailMergeDisplayColumns()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim recordIdColumn As Long
    Dim dateSourceColumn As Long
    Dim amountSourceColumn As Long
    Dim dateOutputColumn As Long
    Dim amountOutputColumn As Long
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim recordId As String
    Dim dateValue As Variant
    Dim amountValue As Variant
    Dim dateSerial As Double
    Dim dateText As String
    Dim amountText As String
    Dim dateOutputs() As String
    Dim amountOutputs() As String
    Dim oldCalculation As XlCalculation
    Dim oldScreenUpdating As Boolean
    Dim oldEnableEvents As Boolean
    Dim oldStatusBar As Variant
    Dim stateCaptured As Boolean
    Dim completedRows As Long

    On Error GoTo CleanFail

    Set wb = ThisWorkbook
    If LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
        Err.Raise vbObjectError + 2100, , _
            "Save this workbook as an Excel Macro-Enabled Workbook (.xlsm) before running the helper."
    End If

    If wb.ReadOnly Then
        Err.Raise vbObjectError + 2102, , _
            "The workbook is read-only. Save an editable .xlsm copy first."
    End If

    Set ws = RequireWorksheet(wb, DATA_SHEET)
    If ws.ProtectContents Then
        Err.Raise vbObjectError + 2103, , _
            "The Data sheet is protected. Use an approved editable copy."
    End If

    recordIdColumn = RequireHeaderColumn(ws, HEADER_RECORD_ID)
    dateSourceColumn = RequireHeaderColumn(ws, HEADER_DATE_SOURCE)
    amountSourceColumn = RequireHeaderColumn(ws, HEADER_AMOUNT_SOURCE)
    dateOutputColumn = RequireHeaderColumn(ws, HEADER_DATE_OUTPUT)
    amountOutputColumn = RequireHeaderColumn(ws, HEADER_AMOUNT_OUTPUT)

    lastRow = ws.Cells(ws.Rows.Count, recordIdColumn).End(xlUp).Row
    If lastRow < FIRST_DATA_ROW Then
        Err.Raise vbObjectError + 2101, , _
            "No records were found on the " & DATA_SHEET & " sheet."
    End If

    oldCalculation = Application.Calculation
    oldScreenUpdating = Application.ScreenUpdating
    oldEnableEvents = Application.EnableEvents
    oldStatusBar = Application.StatusBar
    stateCaptured = True

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' Check every source record before changing output cells.
    ReDim dateOutputs(FIRST_DATA_ROW To lastRow)
    ReDim amountOutputs(FIRST_DATA_ROW To lastRow)

    For rowNumber = FIRST_DATA_ROW To lastRow
        Application.StatusBar = "Preparing Mail Merge display values: row " & _
                                rowNumber & " of " & lastRow

        If IsError(ws.Cells(rowNumber, recordIdColumn).Value2) Then
            RaiseRowError rowNumber, HEADER_RECORD_ID, _
                "The record ID contains an Excel error."
        End If
        recordId = Trim$(CStr(ws.Cells(rowNumber, recordIdColumn).Value2))
        If Len(recordId) = 0 Then
            RaiseRowError rowNumber, HEADER_RECORD_ID, "A record ID is required."
        End If

        dateValue = ws.Cells(rowNumber, dateSourceColumn).Value2
        If IsError(dateValue) Then
            RaiseRowError rowNumber, HEADER_DATE_SOURCE, "The source cell contains an Excel error."
        ElseIf Len(Trim$(CStr(dateValue))) = 0 Then
            dateText = vbNullString
        ElseIf VarType(dateValue) <> vbDouble Then
            RaiseRowError rowNumber, HEADER_DATE_SOURCE, _
                "Use a real Excel date value, not ambiguous date text."
        Else
            dateSerial = CDbl(dateValue)
            If wb.Date1904 Then dateSerial = dateSerial + 1462#

            If dateSerial < 61 Or dateSerial > 2958465# Then
                RaiseRowError rowNumber, HEADER_DATE_SOURCE, _
                    "The date serial is outside the supported range 1900-03-01 through 9999-12-31."
            End If

            dateText = Format$(CDate(dateSerial), DATE_FORMAT_TEXT)
        End If

        amountValue = ws.Cells(rowNumber, amountSourceColumn).Value2
        If IsError(amountValue) Then
            RaiseRowError rowNumber, HEADER_AMOUNT_SOURCE, "The source cell contains an Excel error."
        ElseIf Len(Trim$(CStr(amountValue))) = 0 Then
            amountText = vbNullString
        ElseIf VarType(amountValue) <> vbDouble Then
            RaiseRowError rowNumber, HEADER_AMOUNT_SOURCE, _
                "Use a numeric amount, not preformatted currency text."
        Else
            amountText = FormatCurrencyText(CDbl(amountValue))
        End If

        dateOutputs(rowNumber) = dateText
        amountOutputs(rowNumber) = amountText
    Next rowNumber

    ' No output values have been touched if source validation fails.
    ws.Range(ws.Cells(FIRST_DATA_ROW, dateOutputColumn), _
             ws.Cells(lastRow, dateOutputColumn)).NumberFormat = "@"
    ws.Range(ws.Cells(FIRST_DATA_ROW, amountOutputColumn), _
             ws.Cells(lastRow, amountOutputColumn)).NumberFormat = "@"

    For rowNumber = FIRST_DATA_ROW To lastRow
        ws.Cells(rowNumber, dateOutputColumn).Value2 = dateOutputs(rowNumber)
        ws.Cells(rowNumber, amountOutputColumn).Value2 = amountOutputs(rowNumber)

        If CStr(ws.Cells(rowNumber, dateOutputColumn).Value2) <> dateOutputs(rowNumber) Then
            RaiseRowError rowNumber, HEADER_DATE_OUTPUT, "The written date text did not verify."
        End If
        If CStr(ws.Cells(rowNumber, amountOutputColumn).Value2) <> amountOutputs(rowNumber) Then
            RaiseRowError rowNumber, HEADER_AMOUNT_OUTPUT, "The written amount text did not verify."
        End If
        completedRows = completedRows + 1
    Next rowNumber

    ' Save under the original calculation mode, not forced manual mode.
    wb.Save

CleanExit:
    If stateCaptured Then
        Application.Calculation = oldCalculation
        Application.ScreenUpdating = oldScreenUpdating
        Application.EnableEvents = oldEnableEvents
        Application.StatusBar = oldStatusBar
    End If

    If Err.Number = 0 Then
        MsgBox completedRows & " Mail Merge rows prepared and the workbook saved." & vbCrLf & _
               "Reconnect or refresh the Word data source, preview six records, and then run the full merge.", _
               vbInformation, "Display columns ready"
    End If
    Exit Sub

CleanFail:
    Dim errorMessage As String
    errorMessage = Err.Description
    On Error Resume Next
    If stateCaptured Then
        Application.Calculation = oldCalculation
        Application.ScreenUpdating = oldScreenUpdating
        Application.EnableEvents = oldEnableEvents
        Application.StatusBar = oldStatusBar
    End If
    On Error GoTo 0
    MsgBox "No full Mail Merge run should start yet." & vbCrLf & errorMessage, _
           vbCritical, "Display-column preparation stopped"
End Sub

Private Function RequireWorksheet(ByVal wb As Workbook, _
                                  ByVal worksheetName As String) As Worksheet
    On Error Resume Next
    Set RequireWorksheet = wb.Worksheets(worksheetName)
    On Error GoTo 0

    If RequireWorksheet Is Nothing Then
        Err.Raise vbObjectError + 2110, , _
            "Worksheet not found: " & worksheetName
    End If
End Function

Private Function RequireHeaderColumn(ByVal ws As Worksheet, _
                                     ByVal requiredHeader As String) As Long
    Dim lastHeaderColumn As Long
    Dim columnNumber As Long
    Dim headerText As String
    Dim matchedColumn As Long

    lastHeaderColumn = ws.Cells(HEADER_ROW, ws.Columns.Count).End(xlToLeft).Column

    For columnNumber = 1 To lastHeaderColumn
        headerText = Trim$(CStr(ws.Cells(HEADER_ROW, columnNumber).Value2))
        If StrComp(headerText, requiredHeader, vbTextCompare) = 0 Then
            If matchedColumn <> 0 Then
                Err.Raise vbObjectError + 2111, , _
                    "Duplicate header in row 1: " & requiredHeader
            End If
            matchedColumn = columnNumber
        End If
    Next columnNumber

    If matchedColumn = 0 Then
        Err.Raise vbObjectError + 2112, , _
            "Required header missing from row 1: " & requiredHeader
    End If

    RequireHeaderColumn = matchedColumn
End Function

Private Function FormatCurrencyText(ByVal amount As Double) As String
    Dim absoluteText As String

    absoluteText = Format$(Abs(amount), NUMBER_FORMAT_TEXT)

    If amount < 0 Then
        FormatCurrencyText = "(" & CURRENCY_SYMBOL & absoluteText & ")"
    Else
        FormatCurrencyText = CURRENCY_SYMBOL & absoluteText
    End If
End Function

Private Sub RaiseRowError(ByVal rowNumber As Long, _
                          ByVal headerName As String, _
                          ByVal reason As String)
    Err.Raise vbObjectError + 2120, , _
        "Row " & rowNumber & ", column " & headerName & ": " & reason
End Sub
Important scope

The code prepares and saves the Excel source. If its values come from formulas and calculation is manual, recalculate the source first; this helper does not refresh external connections. It does not open Word, change field codes, execute Mail Merge, or certify the final document. Format$ follows the computer’s regional conventions for numeric separators; the $ symbol and date pattern are explicit constants that you can change.

Technical references: Excel Range.Value2, VBA Format function, and Excel Range.NumberFormat.

Validate six records before the full merge

Preview these cases in Word and inspect the finished merged file. The goal is to test business meaning as well as punctuation.

Ordinary positive amount

Confirm the symbol, thousands separator, decimal places, and date order.

Negative amount

Verify whether the approved output uses parentheses, a leading minus, or another accounting rule.

True zero

Confirm that zero is displayed as zero and is not confused with a missing value.

Blank date or amount

Confirm that blank remains blank and does not become a serial number, zero, or field error.

Invalid source type

Enter an ambiguous text date in a test copy and confirm the helper stops with the row and header named.

Different regional machine

If another computer runs the workflow, compare separators and month names before approving production.

Refresh the connection

Microsoft recommends completing spreadsheet changes before connecting it to Word. After regenerating display columns, save the workbook, reconnect or refresh the data source, and preview the six records again.

Move beyond Mail Merge when every row becomes a separate file

DocxForge Pro Factory is the direct product fit for many Excel rows, one standard Word template, and one separate DOCX or PDF per row. It does not repair an existing Word Mail Merge field; it replaces that batch shape with a local template-based workflow.

One standard template

Use the same approved Word template for every row in the batch.

Many Excel rows

Inspect the structured source before generation and keep display-ready fields explicit.

Separate outputs

Create a distinct document for each row with controlled naming and optional PDF output.

Still requires a pilot

Factory does not decide the correct locale or accounting rule. Test difficult values and review the real DOCX/PDF output.

Factory: inspect the Excel source and generate the batch

DocxForge Pro Factory Excel data screen for reviewing date and currency display fields
Inspect the Excel rows and document-ready values before generation.
DocxForge Pro Factory documents screen for generating separate Word and PDF files
Generate separate outputs from one standard Word template.
Test the date and currency contract on six difficult rows7 days free on the 3-month plan, then $38 every 3 months • 14-day refund after purchase
Factory workflowLocal processingOne Word templateDOCX + optional PDF
Start Free 7-Day Trial

Frequently asked questions

Short answers about Word Mail Merge dates, currency, Excel display columns, and the Factory alternative.

Why does Word show a number such as 45292 instead of an Excel date?

Excel stores dates as serial numbers. A Word Mail Merge connection can expose that stored value instead of Excel’s cell display. Add a Word date-time picture switch or merge a dedicated text column created for document output.

How do I format a Mail Merge date in Word?

Toggle field codes with Alt+F9, edit the existing field to a form such as { MERGEFIELD InvoiceDate \@ "MMMM d, yyyy" }, toggle the codes back, and preview several records. Do not type ordinary braces around a new field.

How do I keep the currency symbol and two decimal places?

Use a Word numeric picture switch such as { MERGEFIELD Amount \# "$#,##0.00;($#,##0.00);$0.00" }, or merge an Excel display-text column when blank handling or locale-specific wording must be controlled before Word reads the data.

Should the original date and amount columns become text?

No. Keep real dates and numeric amounts for calculations, sorting, filtering, and validation. Create separate display columns only for the exact strings sent to Word.

Why does Word still show the old value after I changed Excel?

Save the workbook, then reconnect or refresh the Word Mail Merge data source before previewing again. Microsoft also recommends completing spreadsheet changes before connecting the workbook to the merge document.

Where does DocxForge Pro Factory fit this problem?

Factory is the direct fit when many Excel rows should create separate files from one standard Word template. It does not repair existing Mail Merge field codes; it replaces that batch shape with a local template-based generation workflow.

Keep the source numeric and make the document representation explicitDocxForge Pro 7 days free on the 3-month plan, then $38 every 3 months • 14-day refund after purchase
Excel rowsOne Word templateLocal generationDOCX + optional PDF
Start Free 7-Day Trial