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

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
.

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

John

-----Original Message-----


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
.

.

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

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

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

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" wrote in 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

On Tue, 25 May 2004 21:11:06 -0700, "Steve"
wrote:

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