Microsoft Office Tutorials and References

In Depth Information

**Using the Format Cells Dialog Box**

or one time from another time, the program automatically formats the

calculated result in a corresponding date or time number format as well. For

example, if you enter 8-15-12 in cell B4 and 4/15/12 in cell C4 and in cell D4

enter the following formula for finding the number of elapsed days between

the two dates:

=B4-C4

Excel correctly returns the result of 122 (days) using the General number

format. However, when dealing with formulas that calculate the difference

between two times in a worksheet, you have to reformat the result that

appears in a corresponding time format into the General format. For example,

suppose that you enter 8:00 AM in cell B5 and 4:00 PM in cell C5 and then

create in cell D5 the following formula for calculating the difference in hours

between the two times:

=C5-B5

You then have to convert the result in cell D5 — that automatically appears

as 8:00 AM — to the General format. When you do this, the fraction 0.333333 —

representing its fraction of the total 24-hour period — replaces 8:00 AM in

cell D5. You can then convert this fraction of a total day into the

corresponding number of hours by multiplying this cell by 24 and formatting the cell

with the General format.

Ogling some of the other number formats

Excel supports more number formats than just the Accounting, Comma

Style, and Percentage number formats. To use them, select the cell range (or

ranges) you want to format and select Format Cells on the cell shortcut menu

(right-click somewhere in the cell selection to activate this menu) or just

press Ctrl+1 to open the Format Cells dialog box.

After the Format Cells dialog box opens with the Number tab displayed, you

select the desired format from the Category list box. Some number formats —

such as Date, Time, Fraction, and Special — give you further formatting

choices in a Type list box. Other number formats, such as Number and

Currency, have their own particular boxes that give you options for refining

their formats. When you click the different formats in these list boxes, Excel

shows what effect this would have on the first of the values in the current cell

selection in the Sample area above. When the sample has the format that you

want to apply to the current cell selection, you just click OK or press Enter to

apply the new number format.