Date Formatting

[Acct4life]

Hello and thanks in advance for any help on this.

I have an excel file that has come over from another source. Currently the date columns show for ex:

1312011 for 1/31/2011
10012012 for 10/01/2012
1232012 for 1/23/2012

I need the date to be formatted so that they all show up with the slashes in the order above. Seems like a simple format equation but when i go in to format the sells I just get ################## and nothting will format. I was doing some ready an it might have to do with something like the data source is coming from a Mac, but not sure. Is there anyway to get these cells formatted with the slashes in?

Thanks

[FDibbins]

Hi and welcome to the forum

The way you have downloaded your data makes it almost impossible to convert to dates. For instance, if you take your last sample...
1232012...that could be
1/23/2012
or
12/3/2012
How would you know the difference? Is there any other way that you could pull that data in that could make the dates 01232012 - ie 8 digits?

[martindwilson]

this always gets awkward with different default date systems but
=VALUE(LEFT(TEXT(A1,"00000000"),2)&"/"&MID(TEXT(A1,"00000000"),3,2)&"/"&RIGHT(A1,4)) formate cell as date

[FDibbins]

Martin, would that handle 4, 6, 7 or 8 number dates?

ie
1112 (1/1/2012)
112012 (1/1/2012)
1112012 (11/1/2012 - or 1/11/2012)
10012012 (10/01/2012)

Just curious

[Acct4life]

Marin that worked out perfectly for me thanks for your help. What I did before was did some left pulling and kind or rigged it so I could sort by the dates I needed. Thanks again and thanks everyone for your responses. Do you guys know if this is a common error with dates and Mac systems. I ran into a problem before with the 1904 date system that was sent to us from the same company.

Thanks
Scott

[martindwilson]

fd i assumed only the leading 0 got dropped

[FDibbins]

OK thanks, so 6,7 or 8 digits dates