Use Date Formula for Table Headers

[Mrpoopyface]

Hi everyone,

I need help resolving the following issue:

- Is there a way to force excel to read table headers as dates, and not text?

More Info:

- I converted my raw data source (a cross-tab formatted table with columns for each month) into an excel table, then converted that into a tabular form so that I have one column for all my months

- I then create a pivot table using my tabular data

- The problem is that once I convert my data into an excel table, the dates become permanant text format and I can’t get the pivot table to recognize/group dates

- i can manually change them to dates in my tabular table, but they will revert back to text upon refreshing all because it is linked to the source which outputs dates as text (explained above)

Example (dropbox link): https://www.dropbox.com/s/dq7w748lc...t work with excel tables and pivots.xlsx?dl=0

 

[Peter_SSs]

Welcome to the MrExcel board!

Suggestion:
Add a new column to the table at 2. Heading, say, ActualDate and populate it with the formula =DATEVALUE([@Month]) (You may need to format the column too.)
When you create the PT at step 3, use this new column instead of the old 'Month' column

 

[Mrpoopyface]

Hello

Thanks for the suggestion but wouldn’t everything clear once I refresh all? I want to manually expand the table at step 1, then refresh so step 2 and 3 capture that data

 

[Peter_SSs]

Thanks for the suggestion but wouldn’t everything clear once I refresh all?

Did you try it?

 

[Mrpoopyface]

Should have tried it first before replying! It works great. Thanks so much.

I've now run into another problem if you don't mind answering this too...if I change the name of the headers in my table (in step 1 of my example), and click Refresh All, the data in my table (in step 2) won't update because it can't find the header name originally used. (Expression Error: HEADER NAME cannot be found).

Anyway to get around this without having to re-create all my tables? I have a ton of pivot tables and pivot charts connected to them so it would be a hassle to remake.

 

[Peter_SSs]

Anyway to get around this without having to re-create all my tables?

I'm not sure. What were the steps you took to create the table in step 2?