Using Custom Formats to Control Numbers

If the number formats provided by Excel don’t meet your needs you can easily create your own custom formats.  Excel offers extensive tools for creating very specialized number and date formats.  These capabilities even include the ability to include labels as part of the format.  So, for example, a format could include the label “Sales in Units” as part of the format.

• Select the cells you want to format.
• On the Format menu, click Cells, and then click the Number tab.
• In the Category list, click a category, and then click a built-in format that resembles the one you want.
• In the Category list, click Custom.
• In the Type box, edit the number format codes to create the format you want. (Editing a built-in format does not remove the format.)

 You can specify up to four sections of format codes. The sections, separated by semicolons, define the formats for positive numbers, negative numbers, zero values, and text, in that order. If you specify only two sections, the first is used for positive numbers and zeros, and the second is used for negative numbers. If you specify one section, all numbers use that format. If you skip a section, include the ending semicolon for that section.  Use format codes that describe how you want to display:

• A number
• Date or time
• Currency, percentage, or scientific notation
• Text or spacing

 Number Format Codes

• # displays only significant digits and does not display insignificant zeros.
• 0 (zero) displays insignificant zeros if a number has fewer digits than there are zeros in the format.
• ? adds spaces for insignificant zeros on either side of the decimal point so that decimal points align when formatted with a fixed-width font, such as Courier New. You can also use ? for fractions that have varying numbers of digits.

 So, for example: To have 12 displayed as 12.0 and 1234.568 displayed as 1234.57 you would use the #.0# custom format. To have 44.398, 102.65, and 2.8 with aligned decimals you would use the ???.???  custom format.

 Date and Time Format Codes

To display days, months, and years, include the following format codes in a section. If you use "m" immediately after the "h" or "hh" code or immediately before the "ss" code, Microsoft Excel displays minutes instead of the month.

 If the format contains an AM or PM, the hour is based on the 12-hour clock, where "AM" or "A" indicates times from midnight until noon and "PM" or "P" indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock. The "m" or "mm" code must appear immediately after the "h" or "hh" code or immediately before the "ss" code; otherwise, Microsoft Excel displays the month instead of minutes. 

To Display	Use this Code
Months as 1–12	M
Months as 01–12	Mm
Months as Jan–Dec	Mmm
Months as January–December	Mmmm
Months as the first letter of the month	Mmmmm
Days as 1–31	D
Days as 01–31	Dd
Days as Sun–Sat	Ddd
Days as Sunday–Saturday	Dddd
Years as 00–99	Yy
Years as 1900–9999	Yyyy
Hours as 0–23	H
Hours as 00–23	Hh
Minutes as 0–59	M
Minutes as 00–59	Mm
Seconds as 0–59	S
Seconds as 00–59	Ss
Hours as 4 AM	h AM/PM
Time as 4:36 PM	h:mm AM/PM
Time as 4:36:03 P	h:mm:ss A/P
Elapsed time in hours; for example, 25.02	[h]:mm
Elapsed time in minutes; for example, 63:46	[mm]:ss
Elapsed time in seconds	[ss]
Fractions of a second	h:mm:ss.00

 In addition to the above formatting characters, Excel allows you to use the left and right bracket around any time or date code to make Excel show elapsed time.  For example, let’s assume you have a start time (or date) as a fixed value in a cell and you have a formula that calculates the current time and date (i.e. =now()) in another cell.  You can subtract the beginning time or date from the current time and date and have the elapsed time (or number of days, or number of minutes, or number of seconds, etc.) displayed using the left and right brackets.

 

Using Brackets with Dates to Calculate Elapsed Time

- END -