How to Remove AM and PM from Excel and Word Mail Merge Times
Question details
The user needs to display time values in Word mail merge documents without the AM or PM designation, maintaining a clean 24-hour or hour/minute format.
- Product
- Word, Excel
- Device & OS
- not provided
- Scenario
- Performing a mail merge in Word using a data source from Excel containing time values.
- Observed behavior
- Word pulls in the underlying AM/PM designation from Excel time values, even if the Excel cells are formatted to hide them.
Ensure your Excel data source is saved and closed before performing the mail merge in Word. To view and edit field codes in Word, you can press Alt+F9 on your keyboard.
Use a Date and Time Formatting Switch in Word
The most reliable method is to directly instruct Word how to format the time using a merge field switch.
By default, Word pulls the raw unformatted data from Excel. Adding a specific format switch tells Word to display the time exactly as you define, overriding any default AM/PM settings.
Right-click the mail merge time field in your Word document and select 'Toggle Field Codes', or simply press Alt+F9.
Find the merge field code, which will look similar to { MERGEFIELD Start_Time }.
Click inside the closing bracket and add the time format switch \@ "hh:mm". The code should now look like { MERGEFIELD Start_Time \@ "hh:mm" }.
Right-click the field again and select 'Update Field', or press Alt+F9 to hide the codes. Click 'Preview Results' to verify the AM/PM designation is gone.
Format the Time as Text in the Excel Data Source
If you prefer to fix the issue in Excel before merging, you can convert the time values into pure text strings formatted in 24-hour time.
Easily Manage Mail Merges with WPS Office
WPS Office provides a highly compatible and intuitive Mail Merge feature in WPS Writer and WPS Spreadsheet. You can easily format text, dates, and times exactly how you want them without dealing with complex field codes.
- 1. Prepare Data: Open WPS Spreadsheet and format your time column to 24-hour format or use the TEXT function.
- 2. Start Mail Merge: Open WPS Writer and navigate to the 'References' tab to access the Mail Merge tools.
- 3. Connect Data Source: Click 'Open Data Source' and select your saved WPS Spreadsheet file.
- 4. Insert and Preview: Insert the time merge fields into your document and click 'View Merged Data' to ensure a clean, AM/PM-free display.
Frequently Asked Questions
Why does Word show AM/PM when my Excel data is formatted in 24-hour time?
Word pulls the underlying raw data from Excel, not the visual cell formatting. Because Excel internally stores times as fractions of a 24-hour day, Word defaults to displaying them with an AM/PM designation unless instructed otherwise with a field format switch.
Can I change my Windows regional settings to fix the mail merge time format?
While regional settings govern your system's default time display, changing them is not recommended just for a mail merge. It's much safer and more reliable to use the \@ "hh:mm" field switch directly within your Word document.
How do I toggle field codes in Word to edit the merge field?
You can quickly toggle the display of field codes on and off by pressing Alt+F9 on your keyboard. Alternatively, you can right-click an individual merge field and select 'Toggle Field Codes' from the context menu.
Does the TEXT formula change my actual time data in Excel?
Yes, the =TEXT() formula converts the underlying numeric time value into a static text string. Because of this, you won't be able to use the new text cells in mathematical time calculations, but they will merge perfectly into Word exactly as they look.
