How do you format Excel to show number of hours, not the time of day?

Question

I'm setting up a spreadsheet to keep track of the amount of time I spend on various tasks. My manager doesn't like using decimals (1.0 = 1 hour, 1.5 = 1 hr 30 minutes, etc.) because he has trouble converting the tenths into number of minutes. I need the cells to show hours and minutes plus I need to be able to get the sum of columns and rows. Is there a way to set this up? Whenever I try using time, it converts the number of hours to a time of day (am or pm).

I appreciate all the help I can get for this. Thanks!

Answer 1

The simplest way would be to have separate columns for recording the hours and minutes - call them A & C etc for hours and B & D etc for minutes.  Then you could obtain a row total with this formula:

=INT(A1+C1+(B1+D1)/60)&" Hours "&( A1+C1+( B1+D1 )/60-INT( A1+C1+( B1+D1 )/60 ))*60&" Minutes"

Obviously, extend the cell references to match the number of columns of figures you have.

And a column total with this formula:

=INT(SUM(A1:A2,SUM( B1:B2)/60 ))&" Hours "&(( SUM( A1:A2,SUM( B1:B2)/60)-INT( SUM( A1:A2,SUM( B1:B2)/60)))*60&" Minutes")

Replace the A2 and B2 references with the cell references of your last entry.

If this doesn't suit your needs, please post more details and I will try to modify it accordingly.

Answer 2

Highlight the cells you want to change.  Right click and go to "Format Cells".  The first tab allows you to change it in any way you'd like.

Answer 3

Format the cells with H:MM in custom.  Yes it reads as AM or PM hour of day in the formula bar but it works.  You can use the Autosum to add the columns or rows to add the hours.

Here's some indepth stuff on using excell and date/time:

http://www.mvps.org/dmcritchie/excel/dat...

Answer 4

Format your times as h:mm. It will appear as an AM or PM time in the formula bar, ignore it. To sum columns and rows format your total cells using [h]:mm and enter =SUM(cells to be added) in the totals cells.