Number format is missing from "Field Settings" and I cannot format dates on x-Axis of Pivot chart

Question

Hey,

On Pivot Chart's X-Axis, I cannot adjust the Date format to anything.

My source data is in Power Pivot, the format on PowerPivot matches what I want.

I have attempted the fixes found online:

Change the PowerPivot source format
Remove the field and add it again and change format
Thanks

Answer 1

This is because cells contains blank or text. Check data source and only select the arrange which don't include blank cells.

Answer 2

Excel 365 Pro Plus with Power Pivot and Power Query.
Custom date format for PivotChart.
Share file if need be.
https://www.mediafire.com/file/3l9qujhf5ya9952/01_06_19a.xlsx/file
https://www.mediafire.com/file/w24c51tlntneahj/01_06_19a.pdf/file

Answer 3

@Mor va

According to my test, I could reproduce your issue that if source data is in PowerPivot, the option Number Format cannot be found.

As a workaround, you could try to add the source data just to Excel sheet instead of PowerPivot and then make sure there are no blank cells or cells that containing text.
Since if a field has blank cells or cells that contain text, you might not be able to format the numbers, because that field is treated as Text data type.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
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.

Answer 4

Hi @Mor va

From what I read the formatting options are not the same for Pivots and Power Pivots...
Added 2 calculated columns (Year & Month) to your table as you already have others - if that was for me I would build a Calendar with Power Query (let me know if you want a sample) instead

Your updated workbook is avail. here. Any question let me know and if this solves your problem please mark this post as answer to help others with a similar challenge. Thanks

Answer 5

@Mor va

This may be by design.
If you would like to report this, you could give your feedback on the Excel UserVoice forum.
This website is a place that Microsoft uses to collect users' feedback.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
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.