Converting a decimal number into time

Would like to convert a decimal number into time. Data is supplied as follows:
22.4
46.6
111.8
123.8
Need to convert to [ss].00:
:22.40
:46.60
:71.80
:83.80
Is there a formula I can use for this conversion?

Thanks in advance!

Hi!

Try this:

=TIME(0,0,A1)+MOD(A1,1)/86000

Format as \:[ss].00

Not sure what the backslash is for. Excel adds it automatically.

Biff

"M.A.Tyler" <Great Lakes State> wrote in message
news:F061EDD0-E542-4482-A46A-D4EC5B716592@microsoft.com...
> Would like to convert a decimal number into time. Data is supplied as
> follows:
> 22.4
> 46.6
> 111.8
> 123.8
> Need to convert to [ss].00:
> :22.40
> :46.60
> :71.80
> :83.80
> Is there a formula I can use for this conversion?
>
> Thanks in advance!
>
>

Hold on there a second (no pun intended)!

>> 22.4
>> 46.6
>> 111.8
>> 123.8
>> Need to convert to [ss].00:
>> :22.40
>> :46.60
>> :71.80
>> :83.80

How does 111.8 relate to :71.80 and 123.8 to :83.80 ?

Biff

"Biff" <biffinpitt@comcast.net> wrote in message
news:uyHn8b7fGHA.3572@TK2MSFTNGP04.phx.gbl...
> Hi!
>
> Try this:
>
> =TIME(0,0,A1)+MOD(A1,1)/86000
>
> Format as \:[ss].00
>
> Not sure what the backslash is for. Excel adds it automatically.
>
> Biff
>
> "M.A.Tyler" <Great Lakes State> wrote in message
> news:F061EDD0-E542-4482-A46A-D4EC5B716592@microsoft.com...
>> Would like to convert a decimal number into time. Data is supplied as
>> follows:
>> 22.4
>> 46.6
>> 111.8
>> 123.8
>> Need to convert to [ss].00:
>> :22.40
>> :46.60
>> :71.80
>> :83.80
>> Is there a formula I can use for this conversion?
>>
>> Thanks in advance!
>>
>>
>
>

"Biff" <biffinpitt@comcast.net> wrote in message
news:Ov$T7h7fGHA.4004@TK2MSFTNGP04.phx.gbl...
> Hold on there a second (no pun intended)!

>>> 22.4
>>> 46.6
>>> 111.8
>>> 123.8
>>> Need to convert to [ss].00:
>>> :22.40
>>> :46.60
>>> :71.80
>>> :83.80

> How does 111.8 relate to :71.80 and 123.8 to :83.80 ?
>
> Biff

How about:
1 minute 11.8 seconds = 71.80 seconds
1 minute 23.8 seconds = 83.80 seconds
--
David Biddulph

>>How does 111.8 relate to :71.80 and 123.8 to :83.80 ?

>How about:
>1 minute 11.8 seconds = 71.80 seconds
>1 minute 23.8 seconds = 83.80 seconds

Ah, very sharp!

Biff

"David Biddulph" <david@biddulph.org.uk> wrote in message
news:4475772b$1_1@glkas0286.greenlnk.net...
> "Biff" <biffinpitt@comcast.net> wrote in message
> news:Ov$T7h7fGHA.4004@TK2MSFTNGP04.phx.gbl...
>> Hold on there a second (no pun intended)!
>
>>>> 22.4
>>>> 46.6
>>>> 111.8
>>>> 123.8
>>>> Need to convert to [ss].00:
>>>> :22.40
>>>> :46.60
>>>> :71.80
>>>> :83.80
>
>> How does 111.8 relate to :71.80 and 123.8 to :83.80 ?
>>
>> Biff
>
> How about:
> 1 minute 11.8 seconds = 71.80 seconds
> 1 minute 23.8 seconds = 83.80 seconds
> --
> David Biddulph
>

Biff, David's answer to your question is correct. 111.8 is 1 minute, 11
seconds and 80/100's. Your formula gives an answer of :111.8 I really need
:71.80. Or 1:11.8 then when I convert to [ss].00 I'll get :71.8.

Thanks for your help!

"Biff" wrote:

> Hold on there a second (no pun intended)!
>
> >> 22.4
> >> 46.6
> >> 111.8
> >> 123.8
> >> Need to convert to [ss].00:
> >> :22.40
> >> :46.60
> >> :71.80
> >> :83.80
>
> How does 111.8 relate to :71.80 and 123.8 to :83.80 ?
>
> Biff
>
> "Biff" <biffinpitt@comcast.net> wrote in message
> news:uyHn8b7fGHA.3572@TK2MSFTNGP04.phx.gbl...
> > Hi!
> >
> > Try this:
> >
> > =TIME(0,0,A1)+MOD(A1,1)/86000
> >
> > Format as \:[ss].00
> >
> > Not sure what the backslash is for. Excel adds it automatically.
> >
> > Biff
> >
> > "M.A.Tyler" <Great Lakes State> wrote in message
> > news:F061EDD0-E542-4482-A46A-D4EC5B716592@microsoft.com...
> >> Would like to convert a decimal number into time. Data is supplied as
> >> follows:
> >> 22.4
> >> 46.6
> >> 111.8
> >> 123.8
> >> Need to convert to [ss].00:
> >> :22.40
> >> :46.60
> >> :71.80
> >> :83.80
> >> Is there a formula I can use for this conversion?
> >>
> >> Thanks in advance!
> >>
> >>
> >
> >
>
>
>

"M.A.Tyler" <Great Lakes State> wrote in message
news:8C394AFD-AADD-4E98-A641-95B3956118E6@microsoft.com...
> Biff, David's answer to your question is correct. 111.8 is 1 minute, 11
> seconds and 80/100's. Your formula gives an answer of :111.8 I really need
> :71.80. Or 1:11.8 then when I convert to [ss].00 I'll get :71.8.

In which case one could convert Biff's formula to:
=TIME(0,0,60*INT(A1/100)+MOD(A1,100))+MOD(A1,1)/86000
--
David Biddulph

Thank you Biff and David. Formula is working great, you were both very
helpful!!

"David Biddulph" wrote:

> "M.A.Tyler" <Great Lakes State> wrote in message
> news:8C394AFD-AADD-4E98-A641-95B3956118E6@microsoft.com...
> > Biff, David's answer to your question is correct. 111.8 is 1 minute, 11
> > seconds and 80/100's. Your formula gives an answer of :111.8 I really need
> > :71.80. Or 1:11.8 then when I convert to [ss].00 I'll get :71.8.
>
> In which case one could convert Biff's formula to:
> =TIME(0,0,60*INT(A1/100)+MOD(A1,100))+MOD(A1,1)/86000
> --
> David Biddulph
>
>
>