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

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

Richard Schollar

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
 
R

Richard Schollar

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
 
P

Peo Sjoblom

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
 
M

Meebers

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
 
J

Jacqueline

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
 
C

Conan Kelly

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
 
J

Jacqueline

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert 29.08 hours (shown in decimal form) to time shown in "hh:m 1
converting a decimal to time 3
Convert Decimal Minutes to Hours-Minutes-Seconds 3
Converting a decimal time into hours and minutes 1
How to sum a column to equal hours and minutes in Excel? 1
convert length of time to minutes from hundreths 2
convert hours format to decimal or minutes (exp. 1:15:00 = 75 min. 6
Convert time format to a decimal 2

Top