XL2000: "am/pm" Changed to "AM/PM" in Custom Number Format (212114)

SYMPTOMS

When you use a time format that contains "am" or "pm" in lowercase, such as

h:mm am/pm

the time format automatically changes to "AM" or "PM" in uppercase:

h:mm AM/PM

This also happens when you open a workbook created in a version of Microsoft Excel earlier than Excel 97, and the workbook uses the lowercase format. When you open the workbook in Excel 2000, the formatting for time data in cells or in charts automatically changes from "am/pm" to "AM/PM".

CAUSE

Microsoft Excel 2000 does not directly support custom number formats that use "am/pm" in lowercase.

You can use a lowercase "m" in Excel number formats to designate either minutes or months; therefore, to avoid ambiguity, Excel now automatically changes formats that use "am/pm" to use "AM/PM" instead.

WORKAROUND

To simulate a lowercase "am/pm" format, change the number formatting to a format that combines "a/p" with the letter m, similar to the following:

h:mm a/p"m"

Be sure to include the quotation marks. Because "m" appears as a quoted letter in this format, Excel does not automatically convert it to uppercase.

How Does the Workaround Work?

In addition to the format

AM/PM

Excel allows you to use the format:

A/P

Because the "A/P" format does not contain the letter M, Excel also allows you to use a lowercase version of the "A/P" format:

a/p

The workaround simply adds the literal text string "m" to the end of the "a/p" format to simulate the lowercase "am/pm" format:

a/p"m"

MORE INFORMATION

You can apply a custom time format to cells on a worksheet by using the Format Cells dialog box (on the Format menu, click Cells). In earlier versions of Excel, you could display lowercase am/pm by using a custom number format similar to the following:

h:mm am/pm

For example, in versions of Microsoft Excel earlier than Excel 97, the custom time format h:mm am/pm allowed the time 8:30 A.M. to appear in lowercase, as 8:30 am. However, Microsoft Excel 2000 converts this same custom format to 8:30 AM.

REFERENCES

For more information about custom number formats, click Microsoft Excel Help on the Help menu, type create a custom number format in the Office Assistant or the Answer Wizard, and then click Search to view the topic.