cell format changes value of number

[elkins]

I have imported data that has a column of time (mm:ss.0) and I want to use it in a formula multiplying it with general format data but it changes the value, i.e. it takes 00:25.1 and when I copy and paste that value into a general format cell it shows 0.000291. When I try to use the time in a formula, I cannot get it to use the 25 seconds like I would like. Can someone tell me how to fix this?

Thanks.

[Earl Kiosterud]

Elkins,

0.000291 is the underlying date/time value for 25.1 seconds (00:00:25.1).
It's actually 0.000290509. When you paste that cell, it should be pasting
the time formatting also, but apparently isn't. Are you using a
Paste-Special option? Time formatting would cause that value to appear as
0:25.1, or equivalent, depending on the formatting code used (Format -
Cells - Number - Custom).
--
Earl Kiosterud
www.smokeylake.com

"elkins" <elkins.1w72ib_1128121504.6873@excelforum-nospam.com> wrote in
message news:elkins.1w72ib_1128121504.6873@excelforum-nospam.com...
>
> I have imported data that has a column of time (mm:ss.0) and I want to
> use it in a formula multiplying it with general format data but it
> changes the value, i.e. it takes 00:25.1 and when I copy and paste that
> value into a general format cell it shows 0.000291. When I try to use
> the time in a formula, I cannot get it to use the 25 seconds like I
> would like. Can someone tell me how to fix this?
>
> Thanks.
>
>
> --
> elkins
> ------------------------------------------------------------------------
> elkins's Profile:
> http://www.excelforum.com/member.php...o&userid=27707
> View this thread: http://www.excelforum.com/showthread...hreadid=472244
>

[John F. Collins]

The time is stored in the cell as a number of days. So 25.1 seconds is
0.000291 days. Multiply by 86,400 and you will convert the number of days
into the number of seconds.
John

"elkins" <elkins.1w72ib_1128121504.6873@excelforum-nospam.com> wrote in
message news:elkins.1w72ib_1128121504.6873@excelforum-nospam.com...
>
> I have imported data that has a column of time (mm:ss.0) and I want to
> use it in a formula multiplying it with general format data but it
> changes the value, i.e. it takes 00:25.1 and when I copy and paste that
> value into a general format cell it shows 0.000291. When I try to use
> the time in a formula, I cannot get it to use the 25 seconds like I
> would like. Can someone tell me how to fix this?
>
> Thanks.
>
>
> --
> elkins
> ------------------------------------------------------------------------
> elkins's Profile:
http://www.excelforum.com/member.php...o&userid=27707
> View this thread: http://www.excelforum.com/showthread...hreadid=472244
>

[TonyR]

If you wish to extract the seconds from a date/time value, use =SECONDS(A2)
where the fraction is in A2. So with A2=0.000291 the above would return 25.
--
TonyR


"elkins" wrote:

>
> I have imported data that has a column of time (mm:ss.0) and I want to
> use it in a formula multiplying it with general format data but it
> changes the value, i.e. it takes 00:25.1 and when I copy and paste that
> value into a general format cell it shows 0.000291. When I try to use
> the time in a formula, I cannot get it to use the 25 seconds like I
> would like. Can someone tell me how to fix this?
>
> Thanks.
>
>
> --
> elkins
> ------------------------------------------------------------------------
> elkins's Profile: http://www.excelforum.com/member.php...o&userid=27707
> View this thread: http://www.excelforum.com/showthread...hreadid=472244
>
>

[elkins]

Thanks for the help.