O'Reilly logo

Excel Annoyances by Curtis D. Frye

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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?

You shortchange yourself when you multiply a time serial number by an hourly rate.

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required