Convert decimal hours to time>24hrs

[Guest]

Can someone please tell me how to convert a value formatted as a number and convert it to an hh:mm format (not in text)? eg. 78.73 hours to 78:44

 

[Guest]

I would do a rounddown on the value to get the hours, take
the difference between the value and the roundown to het
the fraction of hour and then multiply by 60 to het the
minutes, and then concatenate them together.

if cell a8 has the value try this:

=CONCATENATE(ROUNDDOWN(A8,0),":",ROUND((A8-ROUNDDOWN(A8,0))
*60,0))

-----Original Message-----
Can someone please tell me how to convert a value

formatted as a number and convert it to an hh:mm format
(not in text)? eg. 78.73 hours to 78:44

 

[Guest]

oh, and then take the value of this result and format cell
as custom [h]:mm

John

 

[Guest]

doesn't that yeild a "text" value? I need to end up with a "time" value for further calculations

 

[Guest]

That still yields a "text" entry that (for me) is unusable for further calcs. Any other ideas??

 

[David McRitchie]

Hi Steve,
Time is a measurement of days. Divide by 24 and format as [h]:mm

More information on Date and Time in
http://www.mvps.org/dmcritchie/excel/datetime.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Can someone please tell me how to convert a value formatted as a number and convert it to an hh:mm format (not in text)? eg. 78.73

hours to 78:44

 

[Guest]

I would be more precise =CONCATENATE((ROUNDDOWN(A8,0),":",INT((A8-ROUNDDOWN(A8,0))*60+.5)) THUS INSTEAD OF 78:43 YOU WILL GET 78:44 AS EXPECTED

 

[Ron Rosenfeld]

Can someone please tell me how to convert a value formatted as a number and convert it to an hh:mm format (not in text)? eg. 78.73 hours to 78:44

Divide by 24
Format as [h]:mm


--ron