Excel formula to convert a general number to date format? 2

[MGJR]

I have a spreadsheet that i need to convert an entire column of "general numbers" into "date format." to give a specific example, I have a cell with "12402" and I need to translate it into the date format 01/24/02. I have looked around, tried to format the cell, however it thinks that this is in serial or Julian format when it is converted. Any help is really appreciated.
MGJR

 

[sfvb]

Here is a formula that will convert the dates. It assumes that all the dates are greater than 12/31/1999, and that the date is in cell A1:

=DATE(MOD(A1,10)+100,INT(A1/10000),INT(MOD(A1,10000)/100))

 

[MGJR]

Thanks for the post SFVB. The text wasnt in Cell A1, however, you know that that is an easy fix. Thanks again for your prompt and expert reply.

 

[red54]

Best I could do on short notice was to use several cells to stip the text into the various parts and then concatenate them back. Using 31402 as bmddyy (b is blank) I got 03/14/02 which will take care of leading blanks in months. ASSUMPTION: day will be 01 or 02 thru 31, i.e. two digits otherwise you'll need to do something different.
using 31402 in a1 then b1 will get ones digit of year:
b1 is =RIGHT(A1)
c1 is =RIGHT((A1-B1)/10) tens digit of year
d1 is =RIGHT((A1-B1-(10*C1))/100) ones digit of day
e1 is =RIGHT((A1-B1-(10*C1)-(100*D1))/1000) tens digit of day
f1 is =RIGHT((A1-B1-(10*C1)-(100*D1)-(1000*E1))/10000) ones digit of month
g1 is =RIGHT((A1-B1-(10*C1)-(100*D1)-(1000*E1)-(10000*F1))/100000) tens digit of month
h1 is =CONCATENATE(G1&F1&"/"&E1&D1&"/"&C1&B1) which gets your date as 03/14/02
Hope this helps.
Time flies when you don't know what you're doing...