cell format changes value of number

  • Thread starter Thread starter elkins
  • Start date Start date
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.
 
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).
 
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
 
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.
 
Back
Top