Mail merge is a useful tool for creating a set of documents that are essentially identical, but where each document contains unique elements. However, when we merge certain emails, we should pay more attention to the format of the date, currency and number. This tutorial shows you how to properly format the date, currency, and number during mail merge in Word. I recently had a case where one of my colleagues made a mail merge from an Excel document. All data was displayed in American format. In Excel, the dates were in British format, but when the data appeared in the mail merge, it was changed to a month day instead of a day day. Hi Karen, thanks for the reply (and sorry for the late reply). This probably has something to do with date formats. The 18.07.1986 format is not an accepted dd-MM-y format, as there is no 18th month. For this reason, the format is not accepted while 7/2/1994 is accepted. Check this last date to make sure the correct date is displayed, as it can be displayed as July 2 (MDY – Month Day Year) or February 7 (DMY – Day Month Year). If you are using the MDY date format (US style): Change the format field to MM-dd-y and see if this solves the problem.
You can change the format of a date field in a Microsoft Word mail merge document by using switches in the field codes. Typically, date formatting issues occur when the source document for the mail merge is an Excel workbook or an Access database. Mail merge date fields can only be changed using switches. A date switch is a field code that changes the format of a date field (e.B MM/DD/YY). I get the correct date format with this code, but the first letter of the month doesn`t appear in uppercase. The date now appears as 12-Dec-2019 instead of 12-Dec-2019Can you advise us on what to do? Using @ characters indicates that this is a date formatting template. In this case, the date is displayed in the same way as “Wednesday, September 26, 2012”. For more information about compiling date templates, see the Word MVP website here: For mail merge fields in date format, yy and YYYY may be different for the same date or timestamp.yy represents the calendar year of the date, while YYYY represents the year of the week. Can you combine two formats? I need to have the date in a certain format (dd-MMM-y) AND I need the abbreviation of the month in capital letters (i.e.
JUN, JAN or MAR) in a direct mail. Thank you. To format a mail merge field, follow these steps: Hello, Brijkishor, this probably means that one of the cells in your Excel column is not formatted as a date. Therefore, mail merge treats all values as a number, not a date. You can try the following to find the error:1. Expand the width of the column and verify that one of the dates is left-aligned.2. Try the following formula in another column: “=year(cell in the original column” and apply it to the entire column. If one of the cells indicates an error, the value of the original column is not a date. Hello, can I add a few days to the date format, for example, I give a discount within 20 days, I need the format to be added today + 20 days.
Is that possible? Thank you If you want to format merge fields with bold, italic, or font colors, you don`t need to customize the mail merge code. Simply select the entire mail merge box and apply the formatting on the Home tab of Microsoft Word on the ribbon. I used it and worked with a normal keyboard. I started using a Lenovo laptop and the Shift + F9 key does not display the merge panel to add the desired format. Can you help us? Thank you note: The terms d, M, y can be arranged in different orders, but the use of the uppercase letter “M” for months is important because the lowercase letter “m” is used for minutes. It is also possible to add text (e.B. /, -, …) and spaces. Just make sure that the added text remains in double quotes. Your email address will not be published. Required fields are marked. For example, the calendar year of the date 2020-12-28 is 2020; on the other hand, the date is Monday, the first day and week of 2021. For the date, the year of the week is 2021.
If you use yyyy as the date format, the year displayed is for 2020-12-28 2020. If you use YYYY as the date format, the year displayed is for 2020-12-28 2021. The following are examples of mail merge date fields formatted using switches. Hello, I am trying to change the format date from 30.06.2019 to June 30, 2019. How can I do this in direct mail? thank you hello I need to change a date to read October 28, 2019, how can I do this? Below is an explanation of how to create even more formats. You can access this information from the Help link in Microsoft Word by typing “Field Format” as the search criteria, and then selecting Insert and Format Field Codes. Tip: You can combine date and time instructions:Day = “d” month = “M” (uppercase “M” = month, lowercase “m” = minutes Year = “y” ” hours = “h” minutes = “m” Estimate help. The field was only displayed as a 5-digit number. It was necessary to use the source information with data in columns in date format, to format the original input data as a table so that Word did not refer to mixed data, or data in the column that was not considered dates, para. B example a reference that retrieves data from another table (it just didn`t work).
The dates must first be entered as a date so that it can be used as a referencing formula in the 2. Table work, which resulted in data not being recognized as dates. After directly referencing the original Word data entered in the first table (already saved as a table [Ctrl + T], so that the top row was not displayed as text), adding the field codes finally worked. In the previous case, you must change the syntax of the merge field to {MERGEFIELD Invoice.Total #,##.00} to use this function to get the expected rendering result of 1,234.00. To resolve this issue, you need a workaround in Word. Essentially, you need to add code to tell Word which format to display. It is also possible to add text (e.B. /, -, …) and spaces, just make sure to stay between the double quotes. This page was very useful, but like Brijikshor, I have two columns as dates and two as six-digit numbers.
I checked and rechecked and formatted all the cells as dates. All other suggestions were gratefully received. It is old, and this solution may have already been addressed. Thank you for putting this information on interwebz! It was incredibly helpful. In case someone has the same problem as me (follow the steps in the post just to find the date unchanged), the solution is simply to update the field. The shift+F9 keyboard shortcut activates a switch. It switches between viewing the merge code and viewing the merge results. To update the mail merge results, right-click the mail merge field. Choose Update Field from the menu that appears. Your merge should now display the results of the date formatting code you entered. 🙂 I use @ “dddd, dd MMMM aaaa”, but it only comes out in word on the day of the week (i.e. “Tuesday” instead of “Tuesday, November 12, 2020”).
I checked the Excel data formatted as date, I don`t know what else to try if someone can help you If all this talk about conversion methods, OLE, DDE and the rest makes your head spin, take a moment and breathe deeply. You`re in luck; There is another way to get the desired date format without having to worry about a data transfer method. You can add a formatting switch to the mail merge field used in your document. If you display the field codes (instead of their results), the field usually looks like this: How can I get the result for the date as a European style: for example: May 2, 2020 Learn with ease! Quickly learn how to format, publish, and share your content with Word 2013. Step by step, you set the pace, build and practice the skills you need, exactly when you need them! Discover Microsoft Word 2013 step by step today! I can choose any type of date format except January 6, 2020.Highlight the field, Shift + F9, add the date format of your choice, highlight the field, Shift + F9 and press the arrow to access the next or previous record. It`s working! The following table provides formatting examples for digital mail merge fields based on different communication profile locale. Before we change the format of the date, number, and currency in the mail merge, we must first apply the following operations. Hello April Johnstone,note it Select the date field you want to change and press Shift + F9,{MERGEFIELD “DATE” @ “MM-dd-y”} The usual codes used in a date switch are d for day, M for month (this must be entered in uppercase M as m is used for minutes) and y for the year as follows: When I use @ “d MMMM aaaa”, I get the results displayed in English. For example, June 25, 2020.
How can I change the language of June to Dutch? Make sure that you don`t have wrong spaces where they shouldn`t be, or that you`ve added the correct spacing for the format. That seems like the trick when I can`t make it work. A date field in a Word mail merge is typically inserted into the main document by using the Insert Merge Field field on the Mail Merge tab of the ribbon. Hi, I tried all this, but I can still see the right date on the sheet, but this is my sending as a number 37018.Do do you know what I`m doing wrong?PS.: Yesterday it worked, today. Modified When defining templates for invoices, credit notes, and debit notes, you can also format digital mail merge fields for the communication profile based on your customers` locale. To enable this feature, follow these steps: Hi Arnold! One or more cells in Excel are likely to contain a text value or a text-formatted number. For this reason, the mail merge does not recognize it as a number/date and for this reason, you cannot change the format in the mail merge.. .