Excel Annoyances

DATE AND TIME ANNOYANCES

DISPLAY PARTIAL HOURS AS DECIMAL NUMBERS

The Annoyance:

I’m a private consultant, and I would like to create a time sheet for myself using Excel. But I’m having a devil of a time getting Excel to subtract time and output total hours to one decimal place. For instance, I formatted all the cells used in the calculation with the hh:mm:ss time format, which means that when it subtracts 7:00:00 from 12:00:00, it shows I worked 5:00:00 hours in the morning (see Figure 3-16) instead of 5.0. When I put in the afternoon time (start 12:30:00, stop 17:00:00, or 4.5 hours worth) it shows the total time I worked as 9:30:00. How do I make Excel display the time worked as decimal values—in this case, 5.0, 4.5, and 9.5 hours?

Figure 3-18. You shortchange yourself when you multiply a time serial number by an hourly rate.

The Fix:

To display a time as a decimal value, such as 9.5 hours, follow these steps:

1. Click the cell where the result is to appear and select Format → Cells, click the Number tab, and select Number from the Category pull-down list.

2. Type this formula in the cell: =HOUR(value)+(MINUTE(value)/60), replacing value with the address of the cell that contains the time or the formula that generates the time you want to convert into a decimal value. In the worksheet shown in Figure 3-16, you would use the formula =HOUR(E4)+(MINUTE(E4)/60).

ROUND HOURS TO THE NEXT TENTH OF ...