Microsoft KB Archive/212114

= XL2000: "am/pm" Changed to "AM/PM" in Custom Number Format =

Article ID: 212114

Article Last Modified on 9/25/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q212114





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"



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



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.

