Change number sequence to date format

[PIVOTGURU]

I'm Using Excel 2010 PowerPivot. I receive various indepdent reports that all send different date structures which I'm combining into one spreadsheet. The easy ones are the ones originating in one of many traditional date structures (like, 09/23/2013). However, the ones I receive via EDI for obvious reasons put the year first then month/day.

Example: Ship Date sent = 20130923
I need: 09/23/2013

I've been able use LEFT, MID, RIGHT to populate a Year, Month column etc.. I thought this would work, however, as I'm building more complexe measures I'm needing a date table so I can calcuate SUM, GROWTH YTD, YTD PRIOR etc..

Thank you for any insight you can provide -I've searched online and in Bill Jelen's book, PowerPivot for the Data Analyst (which is awesome by the way). Thanks again!</SPAN>

 

[cyrilbrd]

I'm Using Excel 2010 PowerPivot. I receive various indepdent reports that all send different date structures which I'm combining into one spreadsheet. The easy ones are the ones originating in one of many traditional date structures (like, 09/23/2013). However, the ones I receive via EDI for obvious reasons put the year first then month/day.

Example: Ship Date sent = 20130923
I need: 09/23/2013

I've been able use LEFT, MID, RIGHT to populate a Year, Month column etc.. I thought this would work, however, as I'm building more complexe measures I'm needing a date table so I can calcuate SUM, GROWTH YTD, YTD PRIOR etc..

Thank you for any insight you can provide -I've searched online and in Bill Jelen's book, PowerPivot for the Data Analyst (which is awesome by the way). Thanks again!

Hi given in A1 your string 20130923 in B1 type =TEXT(A1,"0000\/00\/00")+0 which one formatted to date will return 9/23/13

Would that work for you

 

[PIVOTGURU]

Worked perfectly! Really cool trick - Thank you!

 

[cyrilbrd]

Worked perfectly! Really cool trick - Thank you!

Glad it worked for you, You are welcome.
Thanks for the feedback.