Date Problem

[Shatin]

I have a spreadsheet with data generated by another program. There is one
column with dates in this format: 04-May-05. From what I can tell, Excel is
treat this as a string, not a date.

Next to this column, I use the following formula to convert a date such as
04-May-05 into 04-09-05 (this new column is formatted as yy-mm-dd).

=DATE(RIGHT(D2,2),LOOKUP(MID(D2,4,3),Table!$A$1:$B$12),LEFT(D2,2))

The lookup is for changing Jan into 01, Feb into 02, etc.

PROBLEM:

When I do a pivot table with the column, a date such as 04-May-05 appears
as 1904-09-05, rather than 2004-09-05. What should I do to make sure that
the years are 20xx and not 19xx?

In fact, is there a better way to do the date conversion? My method seems
rather clumsy.

 

[Jim Thomlinson]

Have you tried using the DateValue() function. It takes a single argument
that is a string and (if it can) converts it to a date... So if your string
is in call A1 the in another cell add the formula =datevalue(A1)

 

[Paul Smith]

Try using:

=DATE(20 & RIGHT(D2,2),LOOKUP(MID(D2,4,3),Table!$A$1:$B$12),LEFT(D2,2))

If I have amended your formula correct you should not be passing 2005 rather
than 05 as the year, thus taking out any ambiguity.

 

[Shatin]

Yes, DateValue() works fine! In fact, I did try DateValue initially. What I
got was number. I forgot that I needed to format it into a date format and
so thought it didn't work. Now everything is OK. Thanks!