PDA

View Full Version : Solved: Changing d/m/yyyy format in workbook



lynnnow
05-05-2010, 03:47 AM
Hi,

I've got a formula


=DATEVALUE(MID(C17,SEARCH("/",C17)+1,SEARCH("/",C17,2)-1)&"/"&MID(C17,1,SEARCH("/",C17,1)-1)&"/"& RIGHT(C17,4))
that changes the text of d/m/yyyy to m/d/yyyy and formatted as d/m/yyyy.

I need to do it this way since the data I got is in the text format.

My problem is that if the date is 27/11/2006, the formula works perfectly, but if the date is 3/12/2006, then I get a #VALUE! error. What can I do to accept this kind of dates too.

So far there are only 93 rows of data, but this will increase as the months go by.

Please advise.

Thanks in advance.

Lincoln

Bob Phillips
05-05-2010, 04:21 AM
Why not just do

Data>Text To Columns, go to the third tab in the wizard and select the date option.

lynnnow
05-05-2010, 04:30 AM
Thanks xld. Didn't try that option, though I did convert text to columns earlier.