Display time in easy-to-add decimal form, rather than sticking with Excel's

Displaying time exactly the way you want can be tricky—and it gets even more confusing when you'd like the time to display as a decimal. Often decimals work better for timesheets or other projects when you want to be able to add values together to determine how long you've spent on a project. Let's say you have a worksheet displaying the agenda for a business conference. The start and end times of each presentation are in columns C and D and you'd like the durations of each presentation displayed in column E in decimal form (e.g., 1.25 instead of 1:15). If you'd like to follow along, you can download and extract the file agenda.xls from the URL given at the beginning of this tip.

Let us apply the Time number format to the range C6:D13. Cells E6:E13 will have a similar Time format that excludes AM or PM.

To apply a time number format:

1. Choose Format | Cells from the menu bar and select the Number tab, if necessary. (In Excel 2007 or 2010, choose the Home tab, then click Format in the Cells area, then choose Format Cells from the resulting dropdown list.)
2. Select Time from the Category list box, choose a format from the Type list box, and then click OK.

To display time in a decimal format:

1. Select cell E6 and note the formula, which subtracts the start time of the presentation from its end time.
2. Type =(D6-C6)*24 and press [Enter].
3. Select cell E6 again and click and drag the fill handle to copy the formula down to cell E13.

The values don't look correct, but the formatting we do next will set it straight.
To display the new time format correctly:

1. Select the range E6:E13 and press [Ctrl]1 ([command]1 in 2004) to open the Format Cells dialog box.
2. Choose Number from the Category list box and click OK. Now, each presentation's duration displays as a decimal.

To round the values to tenths of an hour:

1. Select the range of values you'd like to format.
2. Click the Decrease Decimal button on the Formatting toolbar. (In 2007 and 2010, this button is in the Number area of the Home tab.)

Just keep in mind that Excel automatically rounds your values when you decrease the number of digits after the decimal point.