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 |
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 |
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 |
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 __________________________ |
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 __________________________ |
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 |
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 |
All times are GMT +1. The time now is 11:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com