Cell format for total hours:minutes?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I formatted a cell as Custom >> hh:mm and put in 82:25. I get 10:25
displayed in the cell, and 1/3/1900 10:25:00 AM in the formula bar.
How do I get it to display both places as 82 hours and 25 minutes?

Also, in another cell, it's going to have to display as hours-decimal-
tenths of hours. What do I put in that cell to pick up the value and
display it correctly?

Thank you.
Ed
 
Format cell custom as [hh]:mm
without the brackets you will only get what's over after you divide it by 24

3x24 is 72 and 825:25 - 72:00 is 10:25

so you only need to change the format
 
Thank you, Peo! That worked great! Do you have any suggestions on
how to work the second part?

I can't get it to work on what's displayed, because Excel wants to use
its internal (infernal??) date/time value. I managed to get 3.5, but
not 10.5.

Ed
 
Multiply a time value with 24 and format as number or general

--
Regards,

Peo Sjoblom








- Show quoted text -
Click to expand...

Thanks. I'll work with that and come back if it gives me trouble.

Ed
 
Peo Sjoblom said:
Multiply a time value with 24 and format as number or general> --
Regards,
Peo Sjoblom
Click to expand...

When I tried to get decimal equivalents of the hours, I found I had to use
the *number* format.

If I used *general* format, the result I got appeared to be the decimal
equivalent multiplied by 24.
(when using =a1*24, anyway)

Example:


A B C
Hours "general format" "number format"
=a1*24 =a1*24
1 82:25 1978:00 82.42

2 02:35 62:00 2.58
 
If you have a cell with 10:00 and format it as general, that equals

0.416666666666667

now take that times 24 you will get 10

with a number format using the default number format you will get 10.00

No difference there
 
But I think you've forgotten that you'd advised the OP to custom format the
hours column using [hh]:mm as the custom format.

Perhaps I'm terribly confused, but why would you want to then change the
format of that column to "general", after having originally formatted it
"custom" [hh]:mm??

I'm quite new to Excel, but it seems an unnecessarily confusing step to
take, as I believe it was his intent (to follow your example below) to
preserve the original hours entry of 10:00 rather than to change it to
0.416666666666667 as a prelude to decimal expression . . . .
 
On the first worksheet, the number is displayed correctly as 82:25
using [hh]:mm.

On the second worksheet, the cell is formatted Number with 1 decimal
and correctly shows 82.4 using
=A1*24.

Thanks to both of you for your help.

Ed
 
Back
Top