Elmira, Ontario | Well, here's a formula that might work to do a conversion from a text date to a true date.
=DATE(RIGHT(A1,LEN(A1)-FIND("/",A1,FIND("/",A1)+1)),MID(A1,LEN(LEFT(A1,FIND("/",A1)-1))+2,FIND("/",A1,LEN(LEFT(A1,FIND("/",A1)-1))+2)-LEN(LEFT(A1,FIND("/",A1)-1))-2),LEFT(A1,FIND("/",A1)-1)) It's a bit complicated because I didn't know if your day and month were always padded with a leading zero if they were single digits. This formula can handle both '1' and '01' as days or months. If the dates you have always had the leading zero ('01'), things would look a lot simpler.
I couldn't make it work with a 2 digit year. For some reason, it assumes you're starting from 1900 when only including 2 digits for the year. With some work, I'm sure I could come up with a way to solve this one but I think it involves a serious amount of logic to determine which century was closer to the 2 digit year. The formula was already out of hand! |