Solved: Problems converting date into required format

[Nick_London]

Hi,

I have the following date appearing in b3 of my sheet which comes from a web query: 01/05/2009

However this date is incorrect and should actually be 09/05/2009. The actual date on the web page appears as 09 May - i.e it has no year specified hence this probably explains why the date is appearing how it is.
I'm using the date function below to reconstruct the data into what it should be but it returns 03-Nov-2011.

The date function syntax is:

=DATE(2009,MID(B3,4,2),RIGHT(B3,2))

Can anyone suggest if there's another way of getting what I need?

Thanks,

Nick

[Bob Phillips]

Can't say that I know that this will work in all cases

=DATEVALUE(2009&TEXT(B3,"\-yy\-mm"))

[Nick_London]

Hi XLD,

You're right, it doesn't work but it returns 05/09/2009 which is better than 03-Nov-2011!

Nick

[Aussiebear]

=DATEVALUE(2009&TEXT(B3,"\-mm\-yy"))

[Bob Phillips]

Got it the wrong way around

[Nick_London]

Just tried switching the formula to =Datevalue(2009&Text(B3,"\-M-YY")) and this works!