How to change the format of a date in a Pivot Table?

2

1

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:

Source Data

However, my pivot table is showing a different format (presumably [$-en-US]d-mmm;@) by default:

enter image description here

I tried changing the format of those cells:

  1. The clean way: by selecting the parameters of the field, then format, and using YYYY-MM-DD
  2. 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.

piko

Posted 2018-04-04T10:24:52.347

Reputation: 814

No answers