3
1
My pivot pulls from an external data source with a date column. Excel doesn't see this column as a date and the 'Format Cells' option panel doesn't change how the dates are displayed. The cell data is left-aligned, suggesting a string rather than a date. I have tried cast(myvar as date) and convert(varchar, myvar, 101) and convert(varchar, myvar, 1) in the base table, but none of these have been picked up by Excel as dates.
If the column is recognized as a date, I can group by week and month. I understand that if I can't fix this, the next step is to add columns with weeks and months for each date to the table, but I'd like to give formatting the column one more shot before doing that.
Understand that an Excel date is implemented as a floating-point number whose integer portion is the number of days since some epoch and the fraction is the fraction of 24 hours represented by the time. I'm thinking there's some sort of "interpret date" function so you could read the string date into a hidden cell and then apply the function to it to get an Excel date (but I've never tried that). – Daniel R Hicks – 2014-11-28T13:32:56.240
I should also note that I tried using Text-To-Columns, but I get the error that I can't move parts of the Pivot Table inside the pivot report. – Michael K – 2011-02-03T19:21:21.927
Changing the various cast methods does alter the string that excel shows in the pivot but does not change whether or not excel sees that cell as a date. – Michael K – 2011-02-03T19:48:56.937