format date

[ABCD]

I have a column with data as shown below:

1 Jun 2007 06:00:50:000
1 Jun 2007 06:00:55:000
1 Jun 2007 06:01:00:000
1 Jun 2007 06:01:05:000
1 Jun 2007 06:01:10:000



I would like to format this data so that I can call the first data point (1
Jun 2007 06:00:50:000) as 0 seconds, and the following data points as 5
seconds, 10 seconds etc). The data is not all exactly separated by 5
seconds. I have some 45000 rows of data.

 

[Guest]

Assuming the data is in Column A, maybe try something like this:

In B1, enter this formula then fill down as necessary:

=DATEVALUE(LEFT(TRIM(A1), LEN(TRIM(A1))-12)) + TIMEVALUE(MID(TRIM(A1),
LEN(TRIM(A1))-12+1, 8))

In C1, enter this ofrmula then fill down as necessary:

=B1-B$1


Give column C a custom format like

s "seconds"

 

[Guest]

If the data in A1 and A2 is:

1 Jun 2007 06:00:50:000
1 Jun 2007 06:00:55:000

then in B2 enter:
=(A2-A1)*86400 format as General and copy down

This will work only if the date/times are "true" dates. To test this, just
format A1 to General and you should see:

39234.2505787037

 

[Guest]

I also assumed that your data is text and not real dates. If they are real
date values, then all you'll have to do is in B1, enter this formula and fill
down as needed:

=A1-A$1

Then give column B a custom format like

s "seconds"