8 digit date (text with leading zeros) to date format?

Hello all,

I am trying to get a column of 8 digit text to dates.

The text to column wizard doesn't work because I have leading zeros.

The dates are in the format MMDDYYYY in text.

I saw some formulas on here but they are all with YYYY first, and I don't know how to change the formulas to the YYYY last.

Can anyone help with a formula?

Thanks much

Assuming the date is in cell A2, then you can use this formula (e.g. in B2):

=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,3,2))

Apply a date format to that cell as you would prefer, then copy it down.

Hope this helps.

Pete

Yikes man, sorry but that leading zero is messing up that formula also. It works with a date such as 11112011 to 11/11/2011 but it doesn't work with a date like 08222011 to either 08/11/2011 or 8/11/2011.

Maybe the cell is just formatted to show a leading zero when the real value is only 7 digits. If so, try this formula:

=DATE(RIGHT(A2,4),LEFT(A2,IF(LEN(A2)=8,2,1)),MID(A2,IF(LEN(A2)=8,3,2),2))

Hope this helps.

Pete

This did it. Thanks!!

Sorry, forgot to reply with quote. Pete_UK's last formula worked!