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

Mor va 6 Reputation points
2020-12-11T14:35:25.817+00:00

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

47395-capture.png47396-format-to-change.png47286-powerpivot-is-okay.png

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,806 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,637 questions
{count} vote

5 answers

Sort by: Most helpful
  1. Alvin Cai 6 Reputation points
    2022-01-24T03:27:18.88+00:00

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

    1 person found this answer helpful.

  2. Herbert Seidenberg 1,191 Reputation points
    2020-12-12T02:20:44.067+00:00

    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


  3. Erin Ding-MSFT 4,456 Reputation points
    2020-12-14T08:23:12.887+00:00

    @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.


  4. Lz._ 8,986 Reputation points
    2020-12-14T18:04:16.35+00:00

    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

    47947-demo.png

    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

    0 comments No comments

  5. Erin Ding-MSFT 4,456 Reputation points
    2020-12-24T02:09:44.183+00:00

    @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.

    0 comments No comments