Date function- Help needed

Hi,

Try the following formula in D2:

=IFERROR(A2*1,DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)))

Drag this across and down the cells. See the attached file.

Thanks #cbatrody . But the dates are already in mmddyyyy. According to your formula there are few changes as like in D11 the date has changed.

So, is D11 not 14-Jan-2013?

Also, in your file A2 is "11/01/2010" Is it "11-Jan-2010" or "01-Nov-2010"?

D11 is 14-Jan-2013. But A2 is 01-Nov-2010. You may think as already they all are in same format as 'mmddyyyy'. But the problem happens when i try to convert them to 'dd-mon-yyyy'. Only very few get converetd. and others remain same.

Hi,

Try the following formula in D2:

=IFERROR(DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)))

See the attached file.

Thanks cbatrody!!