How to convert time expressed as a decimal to actual hours or minu

  • Thread starter Thread starter ascottbag-hcm
  • Start date Start date
A

ascottbag-hcm

I am receiving a report from a vendor that lists call time minutes of usage
in decimal form, i.e. 5.15. When I try to convert this figure into actual
time format using the formula, =TEXT(G36/24,"H:MM:SS"), my return is 5:09:00.
It appears this formula is returning the amount of time after midnite and not
what I am looking for, which is the time expressed in actual hours and or
minutes used.
 
Hi

Is 5.15 equivalent to 5 minutes and 15 seconds? If so, you could use the
following to convert to Excel time (remember to format the formula cell in a
time format):

=(A1+MOD(A1,1)*40/60)/1440

1440 being the number of minutes in a day.

Richard
 
Peo

As my reply asked, it depends on what is represented by the .15. If it
means 15/100 of a minute then your formula is indeed the correct one.

Richard
 
Yes you are correct, sometimes I read questions too fast. I just went with
the what the
OP tried with his TEXT formula.

--


Regards,


Peo Sjoblom
 
Ok here is my guess. 5.15 minutes is equal to 5 minutes and 9 seconds. I
would use =G36/1440 and format as mm:ss. The answer = 05:09
 
Hi Richard,
I know this was posted a long time ago, but I have a simular problem only
with hours and minutes.

I need to convert numbers like 33.33 to an hour:minute signature. Can you
help me with this?
Thanks much
Jacqueline
 
Jacqueline,

If you need it to be in text, just use your formula from your original post:

=TEXT(G36/24,"H:MM:SS")

But, if you want to use the time in caculations, simplify it like this:

=G36/24

....then format the cell with one of the time formats.

HTH,

Conan Kelly
 
Thanks, this worked :)

Conan Kelly said:
Jacqueline,

If you need it to be in text, just use your formula from your original post:

=TEXT(G36/24,"H:MM:SS")

But, if you want to use the time in caculations, simplify it like this:

=G36/24

....then format the cell with one of the time formats.

HTH,

Conan Kelly
Click to expand...
 
Back
Top