Excel 2010 Bible

Part IV: Using Advanced Excel Features

566

To make the negative numbers more prominent, you can add a color code to the negative part of

the number format string:

0.00_-;[Red]0.00-

Formatting dates and times

When you enter a date into a cell, Excel formats the date using the system short date format. You

can change this format by using the Windows Control Panel (Regional and Language Options).

Excel provides many useful, built-in dates and time formats. Table 24.9 shows some other date

and time formats that you may find useful. The first column of the table shows the date/time serial

number.

TABLE 24.9

Useful Built-In Date and Time Formats

Value Number Format Display

40360 mmmm d, yyyy (dddd) July 1, 2007 (Thursday)

(^40360) “It’s” dddd! It’s Thursday!
40360 dddd, mm/dd/yyyy Thursday, 07/01/2010
40360 “Month: “mmm Month: July
40360 General (m/d/yyyy) 40360 (7/1/2010)
0.345 h “Hours” 8 Hours
0.345 h:mm o’clock 8:16 o’clock
0.345 h:mm a/p”m” 8:16 am
0.78 h:mm a/p”.m.” 6:43 p.m.
Cross-Reference
See Chapter 13 for more information about the Excel date and time serial number system.

Displaying text with numbers

The ability to display text with a value is one of the most useful benefits of using a custom number

format. To add text, just create the number format string as usual (or use a built-in number format

as a starting point) and put the text within quotation marks. The following number format string,

for example, displays a value with the text (US Dollars) added to the end:

#,##0.00 “(US Dollars)”