Convert decimal hours to time>24hrs

S

Steve

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
 
G

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
Click to expand...
formatted as a number and convert it to an hh:mm format
(not in text)? eg. 78.73 hours to 78:44
 
S

Steve

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

Steve

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

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

Steve said:
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
Click to expand...
hours to 78:44
 
T

Tony

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
 
R

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
Click to expand...


Divide by 24
Format as [h]:mm


--ron
 
Top