Formatting Excel Dates 1

[kabushnell]

I have a spreadsheet that is an exported file from another software. The dates that were exported are in a "different" format.

For example the date is exported as 20701 is the date 01/07/2002. Another one is 960226 which is 02/26/1996. Another one looks like 617 which is the date 06/17/2000.

I am trying to find an easy way to format these dates so they will be like 01/07/2002. Does anyone know an easy way to do this. In the past I have used mid, left, right and then concantenate but it takes awhile doing it that way.

 

[kabushnell]

I wanted to make a correction to my post. The first example 20701 should be 07/01/2002

 

[yogia]

Hi kabushnell:

If understand your specifications correctly, and the three dates you have posted are in cells A1:A3, then one of the ways I can do what you are trying to do with a formula based approach is to write the following formula in cell B1 ...

=IF(LEN(A1)<4,20000000,IF(--(LEFT(A1,LEN(A1)-4))<30,20000000,IF(LEN(A1)<3,20000000,19000000)))+A1

Then copy this formula to cells B2:B3

Then select cells B1:B3, do EDIT|Copy , the select cell C1 and do EDIT|PasteSpecial Values

Then as the final step, select cells C1:C3, do DATA|Text_to_Columns, then Next, and in the third screen, in Column_Data_Format choose the Option Date, and in the associated DropDownBox, choose YMD and then click Finish -- the entries in cells C1:C3 will be converted to Date entries mm/dd/yyyy.

I hope this helps!