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
__________________________
|