I have a CSV file that I have imported into excel that originated in an older DOS based program. The date comes across in a format of 101202 being December 2 2010. I need to get it into a format that Excel recognizes as a date.


Any help greatly appreciated.
Thanks
Steve

Maybe like this.

Attached Files

Reformatting.xlsx (9.4 KB, 10 views)

The solution you gave me, thank you very much, gives me February 12th 2010 instead of December 2 2010. I will try and figure out how to fix it as well.
Thanks
Steve

Got it figured out. It now works, Thanks.
Can you explain what the forumula does. What are the command portions.
Thanks
Steve

The problem you experienced may be due to the fact that I use Danish settings.

The formula: =--(RIGHT(A1,2)&"-"&MID(A1,3,2)&"-"&LEFT(A1,2)) where A1 = 101202 does the following:

RIGHT(A1,2) takes the two rightmost digits of 101202 that is 02 and concatenates this with a hyphen (which is what I use as separator between day month and year). This is then concatenated with the result of MID(A1,3,2) which takes two digits from 101202 starting at position 3. This again is concatenated with first a hyphen and then with the result of LEFT(A1,2) which takes the two leftmost digits of 101202, that is 10. The result being: 02-12-10 which is a text string. To convert this text string to a date I put double minus immediately after the equal sign. First minus sign convert the text string to a (negative) number and the second minus sign convert the negative number to a positive (date)= 02-12-2010 which on my system means 2nd December 2010.

You can check that conversion by highlighting (in the Formula field) exactly the following part of the formula:

-(RIGHT(A1,2)&"-"&MID(A1,3,2)&"-"&LEFT(A1,2)) that is with only one of the minus signs and press F9. You will see -40514 where 40514 is the serial number for the date 2nd December 2010. If you next try to highlight the whole formula including both minus signs and press F9 you will see the result 40514.