Convert decimal to hr.mm
 

I have to take a decimal like 22.59 which is already in hr.mm (22 hrs 59 min)
and be able to add and subtract values for a cumulative total in hr.mm
format. This seems too simple but for some reason when I apply previous
suggestions I've found in earlier posts it keeps giving me a VALUE error. Any
suggestions?

Convert decimal to hr.mm
 

=INT((TIME(INT(A1),MOD(A1,1)*100,0)-TIME(INT(A2),MOD(A2,1)*100,0))*24)+
(MOD((TIME(INT(A1),MOD(A1,1)*100,0)-TIME(INT(A2),MOD(A2,1)*100,0))*24,1)*60/100)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Clekn" wrote in message
...
I have to take a decimal like 22.59 which is already in hr.mm (22 hrs 59
min)
and be able to add and subtract values for a cumulative total in hr.mm
format. This seems too simple but for some reason when I apply previous
suggestions I've found in earlier posts it keeps giving me a VALUE error.
Any
suggestions?

Convert decimal to hr.mm
 

=TIME(A1,100*MOD(A1,1),0) if your times are no more than 23:59.
=INT(A1/24)+TIME(A1,100*MOD(A1,1),0) if it might be more than 24 hours.
Remember that if it goes beyond 24 hours, to see the hours without the 24
hour wrap-round you'd need to format the relevant cells as [h]:mm, rather
than h:mm.
--
David Biddulph

"Clekn" wrote in message
...
I have to take a decimal like 22.59 which is already in hr.mm (22 hrs 59
min)
and be able to add and subtract values for a cumulative total in hr.mm
format. This seems too simple but for some reason when I apply previous
suggestions I've found in earlier posts it keeps giving me a VALUE error.
Any
suggestions?

Convert decimal to hr.mm
 

If the time is more (or less also) than 24 hours **and** if the OP's time
format is as shown (2-places for the minutes, meaning single digit minute
values have leading zeroes), then I think this shorter formula can be
used...

=--SUBSTITUTE(A1,".",":")

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=TIME(A1,100*MOD(A1,1),0) if your times are no more than 23:59.
=INT(A1/24)+TIME(A1,100*MOD(A1,1),0) if it might be more than 24 hours.
Remember that if it goes beyond 24 hours, to see the hours without the 24
hour wrap-round you'd need to format the relevant cells as [h]:mm, rather
than h:mm.
--
David Biddulph

"Clekn" wrote in message
...
I have to take a decimal like 22.59 which is already in hr.mm (22 hrs 59
min)
and be able to add and subtract values for a cumulative total in hr.mm
format. This seems too simple but for some reason when I apply previous
suggestions I've found in earlier posts it keeps giving me a VALUE error.
Any
suggestions?