time formula

[Gale Gorman]

I am trying to work with time and do a little math on it.

For example I have 6.56 in h8 and 11.40 in h9.

I can use =(H8 - INT(H8)) * 100 /60 + INT(H8) and return 6.93 in another
cell. Then I can do something similar for H9 and subtract h8 from h9. But
I can't get this to work in a formula confined to one cell.

I would like to use a colon so it will look like the time format but I
cannot get this to work.

Gale Gorman
Houston

 

[Myrna Larson]

If you enter a time with a colon, so Excel recognizes it as such, it will
"under the hood" convert what you typed to a fraction of a day. To subtract
two times, just subtract, i.e. =B1-A1 and format with a time format. If you
need to convert it to hours, multiply by 24. This gives decimal hours, so
don't use a time format.

 

[duane]

have you tried formatting as time

 

[Gale Gorman]

I'll play with that to see what I get.

I am trying to subtract start time from lunch time and enter that in another
cell as hours worked. Then do the same for after lunch and then total each
day for the week.

Thanks,

Gale Gorman
Houston

 

[Gale Gorman]

Yes, and at some point I need to total the time for several days and if it
adds up to more than 24, the result is off by multiples of 24.

Gale Gorman
Houston

 

[Chip Pearson]

Try a custom number format of [hh]:mm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Yes, and at some point I need to total the time for several days and if it
adds up to more than 24, the result is off by multiples of 24.

Gale Gorman
Houston