| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
date problem
I'm stuck with a date problem I can not fix
I import a file that has s date column as follows: 18Oc05 27Se05 22Se05 13De04 6No04 7Oc04 17Ju04 and so on down to 1200 rows, I tried all sorts of cell formats but no luck I need to have the difference in days between two dates. All I get is #VALUE! can some one please help bill gras -- bill gras |
|
#2
|
|||
|
|||
|
plerhaps they are not <dates but texts.
you have to convert text to dates little difficult as the text is written unless you chane each text into m/d/yy particularly oct is written as oc. if they are in csv or text format which can be divided into columns then something can be done. wait for a soluton by mvp "bill gras" wrote in message ... I'm stuck with a date problem I can not fix I import a file that has s date column as follows: 18Oc05 27Se05 22Se05 13De04 6No04 7Oc04 17Ju04 and so on down to 1200 rows, I tried all sorts of cell formats but no luck I need to have the difference in days between two dates. All I get is #VALUE! can some one please help bill gras -- bill gras |
|
#3
|
|||
|
|||
|
Your data shows two characters for the month. Is there any difference betwnn
March and May or between June and July? -- Gary''s Student "bill gras" wrote: I'm stuck with a date problem I can not fix I import a file that has s date column as follows: 18Oc05 27Se05 22Se05 13De04 6No04 7Oc04 17Ju04 and so on down to 1200 rows, I tried all sorts of cell formats but no luck I need to have the difference in days between two dates. All I get is #VALUE! can some one please help bill gras -- bill gras |
|
#4
|
|||
|
|||
|
On Fri, 14 Oct 2005 23:27:02 -0700, bill gras
wrote: I'm stuck with a date problem I can not fix I import a file that has s date column as follows: 18Oc05 27Se05 22Se05 13De04 6No04 7Oc04 17Ju04 and so on down to 1200 rows, I tried all sorts of cell formats but no luck I need to have the difference in days between two dates. All I get is #VALUE! can some one please help bill gras These are just strings not date numbers. You first need to convert them to dates. If the month element is always a 2 character string you need to derive a month number from a lookup table. i.e. Create a table Ja 1 Fe 2 Ma 3 etc in say A1:B12 use the following to extract the month number, =VLOOKUP(MID(A20,LEN(A20)-3,2),A1:B12,2,FALSE) and =Right(A2,2) to get the year and =LEFT(A20,LEN(A20)-4) to get the day. then use the =Date(year,month,day) to create the date number from the results above. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#5
|
|||
|
|||
|
date problem
Hi Richard Buttrey
Thanks for your imput it works great regards Bill -- bill gras "Richard Buttrey" wrote: On Fri, 14 Oct 2005 23:27:02 -0700, bill gras wrote: I'm stuck with a date problem I can not fix I import a file that has s date column as follows: 18Oc05 27Se05 22Se05 13De04 6No04 7Oc04 17Ju04 and so on down to 1200 rows, I tried all sorts of cell formats but no luck I need to have the difference in days between two dates. All I get is #VALUE! can some one please help bill gras These are just strings not date numbers. You first need to convert them to dates. If the month element is always a 2 character string you need to derive a month number from a lookup table. i.e. Create a table Ja 1 Fe 2 Ma 3 etc in say A1:B12 use the following to extract the month number, =VLOOKUP(MID(A20,LEN(A20)-3,2),A1:B12,2,FALSE) and =Right(A2,2) to get the year and =LEFT(A20,LEN(A20)-4) to get the day. then use the =Date(year,month,day) to create the date number from the results above. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#6
|
|||
|
|||
|
date problem
Hi Gary's Student
Thanks for your imput -- bill gras "Gary''s Student" wrote: Your data shows two characters for the month. Is there any difference betwnn March and May or between June and July? -- Gary''s Student "bill gras" wrote: I'm stuck with a date problem I can not fix I import a file that has s date column as follows: 18Oc05 27Se05 22Se05 13De04 6No04 7Oc04 17Ju04 and so on down to 1200 rows, I tried all sorts of cell formats but no luck I need to have the difference in days between two dates. All I get is #VALUE! can some one please help bill gras -- bill gras |
|
#7
|
|||
|
|||
|
date problem
Hi R Venkataraman
thanks for your imput regards Bill -- bill gras "R.VENKATARAMAN" wrote: plerhaps they are not <dates but texts. you have to convert text to dates little difficult as the text is written unless you chane each text into m/d/yy particularly oct is written as oc. if they are in csv or text format which can be divided into columns then something can be done. wait for a soluton by mvp "bill gras" wrote in message ... I'm stuck with a date problem I can not fix I import a file that has s date column as follows: 18Oc05 27Se05 22Se05 13De04 6No04 7Oc04 17Ju04 and so on down to 1200 rows, I tried all sorts of cell formats but no luck I need to have the difference in days between two dates. All I get is #VALUE! can some one please help bill gras -- bill gras |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
| Time Date Formula Problem | Excel Worksheet Functions | |||
| Login Logout Date Problem | Excel Worksheet Functions | |||
| Identifying Date Overlaps | Excel Discussion (Misc queries) | |||
| Date and Time Picker Control problem | Excel Discussion (Misc queries) | |||
Linear Mode
