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

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.

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

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


"ascottbag-hcm" wrote in message
...
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.

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

Why not just

=A1/1440

--


Regards,


Peo Sjoblom

"Richard Schollar" wrote in message
...
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


"ascottbag-hcm" wrote in message
...
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.

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

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

"Peo Sjoblom" wrote in message
...
Why not just

=A1/1440

--


Regards,


Peo Sjoblom

"Richard Schollar" wrote in message
...
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


"ascottbag-hcm" wrote in message
...
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.

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

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

"Richard Schollar" wrote in message
...
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

"Peo Sjoblom" wrote in message
...
Why not just

=A1/1440

--


Regards,


Peo Sjoblom

"Richard Schollar" wrote in message
...
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


"ascottbag-hcm" wrote in
message ...
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.

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

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

"Peo Sjoblom" wrote in message
...
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

"Richard Schollar" wrote in message
...
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

"Peo Sjoblom" wrote in message
...
Why not just

=A1/1440

--


Regards,


Peo Sjoblom

"Richard Schollar" wrote in message
...
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


"ascottbag-hcm" wrote in
message ...
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.

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

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

"Richard Schollar" wrote:

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


"ascottbag-hcm" wrote in message
...
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.

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

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




"Jacqueline" wrote in message
...
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

"Richard Schollar" wrote:

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


"ascottbag-hcm" wrote in message
...
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.

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

Thanks, this worked :)

"Conan Kelly" wrote:

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




"Jacqueline" wrote in message
...
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

"Richard Schollar" wrote:

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


"ascottbag-hcm" wrote in message
...
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.