Fix Hijri Dates and Number Formatting in Word Mail Merge
Question details
Preserve calculated Hijri dates and Excel number formatting when merging spreadsheet data into Word.

- Product
- Microsoft Word and Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user is creating a Word mail merge from an Excel worksheet containing Hijri dates and formatted numbers.
- Observed behavior
- Word may apply its own date and number formatting instead of displaying the formatting shown in Excel.
Identify the Excel columns containing the source dates and numbers, and confirm which columns are used in the Word merge. The Hijri formula below uses an Arabic (Saudi Arabia) locale format, so test it against the date display you need.
Merge Hijri dates from an Excel text helper column
Use a helper column to send the displayed Hijri date to Word as text rather than as a numeric date value.
In the Excel source worksheet, insert a new column and give it a header such as HijriDateText.
For a date in F25, enter =TEXT(F25,"[$-,106]B2d/mm/yyyy;@"). If the Hijri date is calculated from the Gregorian date in G25, use =TEXT(G25,"[$-,106]B2d/mm/yyyy;@") instead. Adjust the cell reference for your first data row.
Fill the formula down the helper column, check that the results show the intended Hijri dates, and save the workbook.
In Word, open the merge document and use Mailings > Select Recipients > Use an Existing List to select the saved workbook if needed. Use Mailings > Insert Merge Field to insert HijriDateText in place of the original date field.
Select Mailings > Preview Results and check several records before selecting Finish & Merge.

Preserve Excel number displays with text helper columns
Use a formatted text column when Word does not display a number with the decimal places or leading zeros you need.
Work with Documents and Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working across documents and spreadsheets. Its Microsoft Office format compatibility helps make the move from Word and Excel seamless.

Frequently Asked Questions
Why does my Hijri date change format in Word mail merge?
Word may apply its own formatting to the underlying Excel date value rather than use the worksheet's displayed format. Convert the date to text in an Excel helper column and insert that column through Mailings > Insert Merge Field.
Will the Hijri text column still work in date calculations?
No. Excel's TEXT function returns text rather than a numeric date. Keep the original date column for calculations and use the helper column for the merge.
How can I keep two decimal places in a merged number?
In Excel, create a helper column with a formula such as =TEXT(H25,"0.00"), fill it down, and save the workbook. In Word, insert that helper column using Mailings > Insert Merge Field and check it with Preview Results.
What if my new helper column does not appear in Word?
Save the Excel workbook, then in Word use Mailings > Select Recipients > Use an Existing List to reselect it. Check Mailings > Insert Merge Field for the new column header.




