Converting value dates to true date format

[john3]

I am new to PowerPivot and have a feed from a MS Access query with date fields in the following format: yyyymmdd (20120101 = January 1, 2012). How do I convert this value formatted date into a date formatted date such as mm/dd/yyyy so that I can evaluate time lags, monthly trends, etc.

Thanks

 

[Jacob Barnett]

John, although the DATE() function from excel works it's probably not the best idea to use it in a calculated column as it could cause performance issues, particularly if you are using that column in a relationship.

Any PowerPivot pro will tell you that you should be using a separate date table related to your fact table, even if that table is only a single column. In this case you could have a second column that contains the date 'serial' to use in the relationship.

Failing that there are a couple of techniques in SQL that you can use to adjust the source data before it even hits PowerPivot - Stackoverflow.com is good place to look for this. Even if you go down this path a separate date table is advised.

Jacob