Extract ID to DOB become backwards

[chaiyya345]

Hi All,
I cant recalled for I have copied this formula from somewhere, but it happens to be backwards. =IFERROR(IF(A2="","",(TEXT(MID(A2,3,4)&LEFT(A2,2),"00\/00\/00")+0)),"Invalid Date")

The ID format is: 75(year), 04(Month), 03(day). I have put the date format to (d-mmm-yyyy).
The DOB displayed as 4-Mar-1975 which is backwards/incorrect. The correct DOB should be 3-Apr-1975. I have changed the MID but no luck to get it fixed.

Any help is greatly appreciated & please find attachment.

Thank-You very much.

[Glenn Kennedy]

One way;

=DATEVALUE(MID(A2,5,2)&"/"&MID(A2,3,2)&"/"&LEFT(A2,2))

[AliGW]

Try this:

=DATEVALUE(MID(A2,5,2)&"/"&MID(A2,3,2)&"/"&LEFT(A2,2))

[chaiyya345]

Hi Glenn, AliGW,

Thanks for your help. Yes, your formula works.
Will mark this thread to SOLVED & reputation issued to you both.

Thank-You very much.

[Glenn Kennedy]

You're welcome.

[Bo_Ry]

Please try

=--TEXT(20-(LEFT(A2)>"3")&LEFT(A2,6),"0-00-00")

[chaiyya345]

Hi Bo_Ry,
Your formula works too. Issued a reputation to you too.

Thank-You very much