#Format date in word for mac how to
#Format date in word for mac code

Also, Excel has an additional code, “MMMMM”, which returns a single letter abbreviation for the month (e.g., “J, F, M, A, …”). But Word is not so smart, and you must use the capitalized letter to refer to months. In almost all cases, Excel is smart enough to figure out which units you are talking about. In Excel, you can use either “M” or “m” for months or minutes. Unfortunately, there is a slight difference between the way these codes work in Excel and Word in Microsoft Office 2013. As an extra bonus, they can also be used in the “Numeric Switches” in Word Mail-Merge Fields described in the next section. These codes for numbers, dates, and times can be used in the TEXT function or in Excel’s Custom number formats.

#Format date in word for mac code

Thus, the code “00000” will ensure that the leading zero is not truncated from New Jersey postal zip codes. The “#” placeholder will be replaced by a digit only if it is not a leading or trailing zero. A key thing to remember is that both “0” and “#” act as placeholders for digits, but “0” will force leading or trailing zeros to appear. Format Codes for Excel’s TEXT FunctionĪ collection of the most commonly used codes is presented at the right. Formatting options such as font, size, and color do not make the trip. The advantage of formatting numbers and dates as text is that text is transported from Excel into a Word Mail Merge document unmolested. In Excel 2016, they can be used in all formulas except aggregate functions, such as, SUM and AVERAGE.) In older versions of Excel, these “numbers” could not be used in calculations. (Tip: It is a good idea to format the cells containing these formulas in a different style, say, italic, or some unusual color to remind you that these numbers are simply text. Although these codes are identical to those used in the Custom Number formatting feature of Excel, they are rarely seen by most Excel users.įor example, the formulas TEXT(B3, “$#,#0.00”) and TEXT(C5, “M/dd/yy”) convert number and date data into textual data (in this case, “$12,345.67” and “12/01/10”). The downside is that you need to know certain formatting codes. To do this, you need to use Excel’s TEXT function, which enables you to convert a number or date into its equivalent text formatted exactly the way you desire. The first way is to avoid the problem: In the Excel worksheet, insert a column with a formula that converts the Excel numbers or dates into a text format that is exactly the same as what you would want to appear in your Mail Merge document. For example, a sales result in Excel appears as 100 but in Word it suddenly becomes 99.99999999996!įortunately, there are no less than three different ways to get Mail Merge numbers to appear the way they should. One of the perennial annoyances of Mail Merge is its inability to format numbers from an Excel spreadsheet correctly. Unfortunately, the task of learning to master all of its idiosyncrasies can give you countless headaches.

#Format date in word for mac how to

Once you learn how to use it, you can save countless hours of work. It is extremely powerful for creating labels and customized letters, emails, or reports. If you’re modifying a format that includes time values, and you use 'm' immediately after the 'h' or 'hh' code or immediately before the 'ss' code, Excel displays minutes instead of the month.The Mail Merge feature of Microsoft Word is one of my favorite parts of the program. In the Type box, make the changes you want using code from the table below. The changes you make will only apply to the custom format you’re creating. The built-in date format can’t be changed, so don’t worry about messing it up.

Under Type, you’ll see the format code for the date format you chose in the previous step. Go back to the Category list, and choose Custom. You can adjust this format in the last step below. In the Category list, click Date, and then choose a date format you want in Type. In the Format Cells box, click the Number tab. The easiest way to do this is to start from a format this is close to what you want. If you want to use a format that isn’t in the Type box, you can create your own.