• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Date on x-axis in Pivot Chart - can't format|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Date on x-axis in Pivot Chart - can't format|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Date on x-axis in Pivot Chart - can…
sp_PrintTopic sp_TopicIcon
Date on x-axis in Pivot Chart - can't format
Avatar
Chris Walsh

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
September 19, 2021
sp_UserOfflineSmall Offline
1
October 12, 2021 - 2:59 pm
sp_Permalink sp_Print

I'm aware that dates and pivot charts don't always get along but I'm hoping someone will have a new suggestion.

I have a large number of data files with soil moisture values over time, for multiple sites and depths. I also have a reference file with information about the placement and type of each sensor. I am using a power query to combine the data files and reference information, then the Data Model to get everything the was I need it. I want to be able to use pivot chart functionality (slicers and a timeline) to visualise the data. It's not possible to have a pivot chart unless the data are summarised in some way so I added a column in the power query that rounds the measurement date-times to the nearest half hour. My pivot chart averages the moisture readings for each rounded date-time so I can get the curves I'm wanting. The curves look good.

But, I cannot get the x-axis to look reasonable. It insists on showing the full date and time and it won't allow me to set a format.

I understand that the usual procedure is to access the field settings via the pivot table where I should find a Number Format button. However, there is no such button displayed. I'm told that can happen when there are blanks or non-date-time values in the column - but I don't have that, it's all date-times.

Any ideas would be gratefully received.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
October 14, 2021 - 9:24 am
sp_Permalink sp_Print

Hi Chris,

Welcome to our forum!

In Power Pivot you can set the number format for a field in the Power Pivot model on the Home tab of the ribbon.

Mynda

Avatar
Chris Walsh

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
September 19, 2021
sp_UserOfflineSmall Offline
3
October 14, 2021 - 7:46 pm
sp_Permalink sp_Print

Hi Mynda, thanks for the welcome and the reply.

I was able to change the date column in the power pivot data model to display only the date part of the date-time data. However, the corresponding x-axis in the pivot chart stubbornly continues to include the time as well as the date. I refreshed everything, and tried creating a new pivot chart directly from the data model window but still no success. 

Chris

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
October 14, 2021 - 10:26 pm
sp_Permalink sp_Print

Hi Chris,

When you say 'change the date column in the Power Pivot data model to display only the date part' do you mean with the formatting?

Have you tried setting the formatting for the chart axis?

You could try changing the data type in Power Pivot to date only.

Mynda

Avatar
Chris Walsh

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
September 19, 2021
sp_UserOfflineSmall Offline
5
October 15, 2021 - 4:01 pm
sp_Permalink sp_Print

Hi Mynda, re your questions and suggestion:

'When you say 'change the date column in the Power Pivot data model to display only the date part' do you mean with the formatting?'   Yes, I changed the formatting for the field (column) within the data model, using the home tab of the Power Pivot ribbon as you suggested. It now displays as date only. The time component is still there but you don’t see it unless you select a cell.

Have you tried setting the formatting for the chart axis?  Yes, over and over again, hoping for a different result (I'm aware what that implies about my mental state!) The Number section, within the Format Axis panel that pops up for the Pivot Chart, doesn't change the chart format at all. But it does work as expected for the y-axis. I've left it set to 'Linked to source'.

You could try changing the data type in Power Pivot to date only. The date type in Power Pivot is date. (The choices are Test, Date or Decimal Number.) The formatting I've set is yyyy-mm-dd, which is what I'd like to see on my Pivot Chart x-axis. (Actually, I want mm-dd but that's not on the list.)

My searching suggests that the most usually effective way to format a date/time x-axis on a Pivot Chart is to adjust the field settings in the associated Pivot Table. But the Number Format button doesn't appear in the field settings window. I'm told that happens when there are non-date entries or blanks somewhere in the column but I don't have those - it's all date/times.

Thanks for any help!

Chris

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
October 18, 2021 - 3:41 pm
sp_Permalink sp_Print

Hi Chris,

Are you able to share your file or a sample file that illustrates the issue? It's difficult to troubleshoot without seeing the data.

Mynda

Avatar
Chris Walsh

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
September 19, 2021
sp_UserOfflineSmall Offline
7
October 24, 2021 - 4:49 pm
sp_Permalink sp_Print

Hi Mynda,

Sorry for the slow reply. I've attached a pared-down version of what I'm working on. More information can be found on the 'notes' sheet in the file.

Thanks!

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
October 25, 2021 - 10:21 am
sp_Permalink sp_Print

Hi Chris,

Thanks for sharing your file. If you want the chart to show every data point at the time level of detail, then the best you can do is nest the axis labels to show both months and time. You cannot change the axis more than this. See chart in file attached on sheet PC.

However, if you're happy to aggregate the data into days, then you can add a column to your source data that extracts the mm-dd values from the date time column and then use that in your chart. See file attached on sheet PT (2).

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Chris Walsh

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
September 19, 2021
sp_UserOfflineSmall Offline
9
October 26, 2021 - 2:07 pm
sp_Permalink sp_Print

Hi Mynda,

Thanks for looking at this. Sadly, those two options match what I had arrived at before I came searching for help! Aggregation to days sacrifices too much resolution so we'll probably just have to live with the full date-times. A bit frustrating but now at least I'll stop wondering if I could do better.

I also expect to be using this helpful guide https://www.myonlinetraininghu.....s#comments and will give credit in my documentation - thanks again,

Chris

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
October 26, 2021 - 3:33 pm
sp_Permalink sp_Print

Hi Chris,

Another option is to reference the PivotTable with formulas to recreate the source data where you can apply the number format you want. You can then insert a regular chart referencing this data. 

If you expect the PivotTable to grow, you can extend your formulas past the end of the PivotTable to allow for growth and then use a dynamic named range for your chart to reference. 

See file attached on PT sheet with an example using one port.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Chris Warren
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27246

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.