format date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ABCD - 19 Sep 2007 16:25 GMT

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.

Reply to this Message

Vergel Adriano - 19 Sep 2007 17:26 GMT

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"

Signature

Hope that helps.

Vergel Adriano

> I have a column with data as shown below:
>
[quoted text clipped - 8 lines]
> seconds, 10 seconds etc). The data is not all exactly separated by 5
> seconds. I have some 45000 rows of data.

Reply to this Message

Vergel Adriano - 19 Sep 2007 17:34 GMT

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"

Signature

Hope that helps.

Vergel Adriano

> Assuming the data is in Column A, maybe try something like this:
>
[quoted text clipped - 23 lines]
> > seconds, 10 seconds etc). The data is not all exactly separated by 5
> > seconds. I have some 45000 rows of data.

Reply to this Message

Gary''s Student - 19 Sep 2007 17:26 GMT

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

Signature

Gary''s Student - gsnu200745

> I have a column with data as shown below:
>
[quoted text clipped - 8 lines]
> seconds, 10 seconds etc). The data is not all exactly separated by 5
> seconds. I have some 45000 rows of data.

Reply to this Message