PDA

View Full Version : Solved: Problems converting date into required format



Nick_London
05-19-2009, 04:47 AM
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
05-19-2009, 04:54 AM
Can't say that I know that this will work in all cases

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

Nick_London
05-19-2009, 07:58 AM
Hi XLD,

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

Nick

Aussiebear
05-19-2009, 08:05 AM
=DATEVALUE(2009&TEXT(B3,"\-mm\-yy"))

Bob Phillips
05-19-2009, 08:15 AM
Got it the wrong way around:doh:

Nick_London
05-19-2009, 08:16 AM
Just tried switching the formula to =Datevalue(2009&Text(B3,"\-M-YY")) and this works!