I'm having an issue in my pivot table (Excel 2016).
My source data contains actual dates (generated with EOMONTH function), in a standard YYYY-MM-DD format:
However, my pivot table is showing a different format (presumably [$-en-US]d-mmm;@) by default:
I tried changing the format of those cells:
- The clean way: by selecting the parameters of the field, then format, and using
YYYY-MM-DD - The dirty way: by selecting the cells, and changing their format to
YYYY-MM-DD
None of these worked, however. The format remains unchanged. I also tried using a different format, like "Currency", but it didn't work.
Interestingly, I tried the same on the "Employee ID" field, which is a number, and I could format it to anything (Currency, Number...) in the pivot table.
When used as a field, why isn't it possible to format a date in a pivot table, while it's possible to format a number? Is there a clean way to change the date format in the pivot table?
I could use the TEXT function, to format the source as text, but then I'd lose the ability to filter on a specific year, as well as the timeline. I could also add another field in the source table, but it's not a very clean solution.
