Convert incorrect date format

QuestionEdit

QUESTION: I need to convert incorrectly fomatted date in column A to properly coded date in column b. Date is either a 5 or 6 digit number, starting from RIGHT TO LEFT, first two digits are for year (no dates are pre-2001), second two are for day, and remaining are for month. I am not to picky on final format for date as long as it is recognized by Excel. I am using2007. Any thoughts?

ANSWER: Joe,

assume the first date is in A1. In b1

=IF(AND(LEN(A1)>=5,LEN(A1)<=6),DATE(1*"20"&RIGHT(A1,2),IF(LEN(A1)=5,MID(A1,1,1)*1,MID(A1,1,2)*1),IF(LEN(A1)=5,MID(A1,2,2)*1,MID(A1,3,2))),"")

that will produce a date serial number. format the cell with the formula to appear as you want your date to appear. then drag fill the formula down the column next to your string/Text dates.

---------- FOLLOW-UP ----------

QUESTION: Wow! Works for the first row, but when I drag I still get the date for the first row in every column. Did I do something wrong?

Joe

AnswerEdit

Joe,

If you used my exact formula, the it should work. If it doesn't it would be an indication that you have Calculation set to manual. Hit the F9 key or change calculation to automatic inoptions

in Excel 2007, click the Office button in the top left in the lower right edge of the resulting dialog, chooseoptions then choose Formulas on the left of the next dialog and the first section should be calculation options. Choose automatic.

If you changed the formula, then remove any dollar signs in the formula.