Convert decimal to hr.mm

C

Clekn

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?
 
B

Bob Phillips

=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)
 
D

David Biddulph

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

Rick Rothstein \(MVP - VB\)

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 said:
=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 said:
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?
Click to expand...
Click to expand...
 
Top