autofill timestamp

[FD]

I have a table with over 4000 records collected with a gps every second, but
the timestamp it gives does not make sense (eg.78419863). I am looking for
some help to calculate the values of a field so that it takes the first
record of the field and adds 1 second to each record after the first time. Is
it possible to have the timestamp read hh:mm:ss

 

[Bernie Deitrick]

FD,

It is - possibly - the number of seconds since midnight of January 1, 2006.

Try using the formula

=DATEVALUE("1/1/2006")+A2/86400

where your timestamp is in cell A2 - format the cell for date and time (or use custom, mmm dd, yyyy
hh:mm:ss) - and see if the timestamps make sense.

If you just want the time part, and not the date, then use

=MOD(A2,86400)/86400

formatted just for time.

The date will be June 26, 2008, and in each case, the time will be 15:17:43.

HTH,
Bernie
MS Excel MVP

 

[FD]

Thanks Bernie. That is exactly what I was looking for.

Cheers,

FD

 

[Bernie Deitrick]

Hey! A lucky guess - I'm gonna have to go out and play the lottery tonight ;-)

Bernie
MS Excel MVP