transforming a text date/year into a date

Hi, I have a file with a text field that says Apr1516, Dec1516 or Jan1516 for example.

I want to create a new column that will translate that into an actual date, so, for example:

Apr1516 would translate to 01/04/2015, ie using the first 2 digits for the year.
Dec1516 would be 01/12/2015, but
Jan1516 would be 01/01/2016 because that should use the last 2 digits for the year.


Any help in terms of how I would automate that would be great.

Thanks
Lucy

Great, thank you.

I changed the formulae to DATE((0+(20&IF(MONTH(1&LEFT(A1,3))<=3,MID(A1,6,2),MID(A1,4,2)))),MONTH(1&LEFT(A1,3)),1)

Thanks!

Please try the following:

Formula:

Please Login or Register  to view this content.

Text field is in ColA