Pivot Table changes format when sliced

Question

I have a pivot table with dates on columns and rows. I format the date with a custom format as shown below:

However, as soon as I start to apply different slicers, the date format reverts back to the original. Yes, I have the option selected to preserve cell formatting on update checked. The data is coming from the data model. I've tried to format the date in the power pivot model, but this particular format is not available and you cannot create a custom format (and I can't use a FORMAT function because that turns it into TEXT so I can no longer perform date math operations and the sorting goes awry. Have tried linked tables in the PP model, but the date continues to revert. Suggestions?

Answer 1

Hi @Dom,

As per my research, I'm afraid this is by design in Excel. Generally, the alternative to this scenario is removing the "date" formatting of the column in the original data table and make the dates text. But as you said above, this solution seems to be impossible in this case.

I suggest you can provide feedback to MS via this portal: https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472. MS would take the feedback into consideration and roll it out if it is highly voted.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.