Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
format date
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
format date
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" -- Hope that helps. Vergel Adriano "ABCD" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
format date
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 -- Gary''s Student - gsnu200745 "ABCD" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
format date
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" -- Hope that helps. Vergel Adriano "Vergel Adriano" wrote: 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" -- Hope that helps. Vergel Adriano "ABCD" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
How do I convert dd/mm/yy date format to yyddd Julian date format | Excel Worksheet Functions | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |