cell format changes value of number

E

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

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

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
 
T

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