Pivot table date format

kristell

New Member
Joined
Dec 12, 2014
Messages
47
Office Version
  1. 2011
Platform
  1. Windows
Hello - hope you are keeping well and can help me. I have 'googled' but all seem to be telling me to do what I have already done and it is not working.

I am using Windows 11 Excel version 2412

My pivot table shows dates from the data source as 01-Jan 02-Jan etc.,

The data in the pivot table is grouped - when not grouped the data shows as 01/01/2025 02/01/2025 etc.,

When I group back by days and it goes back to 01-Jan 02-Jan etc.,

Gone to field settings and changed to 'date' - but it doesn't change

See screenshots attached

Many thanks - in advance!
 

Attachments

  • excel pivot date settings_Page_1.jpg
    excel pivot date settings_Page_1.jpg
    43.5 KB · Views: 10
  • excel pivot date settings_Page_2.jpg
    excel pivot date settings_Page_2.jpg
    43.5 KB · Views: 10
  • excel pivot date settings_Page_3.jpg
    excel pivot date settings_Page_3.jpg
    16.8 KB · Views: 9
  • PIVOT table fields.png
    PIVOT table fields.png
    15.9 KB · Views: 12
is the date in the raw data a real date or text
right click on one of the dates in the raw data itself and change to general - it should be a number

it looks like the date is left justified - which usually means text
 
Upvote 0
You'll need to extract the date part from the field in the source data (or a query linked to it) rather than grouping the dates in the pivot table, as that turns the days into text.
 
Upvote 0
is the date in the raw data a real date or text
right click on one of the dates in the raw data itself and change to general - it should be a number

it looks like the date is left justified - which usually means text
The source data was a date - I used the text to columns and then chose DMY in the options to convert the dates to the correct format
 
Upvote 0
You'll need to extract the date part from the field in the source data (or a query linked to it) rather than grouping the dates in the pivot table, as that turns the days into text.
In the previous month's spreadsheet, the date was formatted correctly - so not sure why it is not doing it this time as everything is the same. - see screenshot
 

Attachments

  • previous pivot.png
    previous pivot.png
    15.9 KB · Views: 9
Upvote 0
Unfortunately a screenshot doesn't really tell us anything. Any chance you can post a copy of the file that worked to onedrive or similar (keep the dates intact but you can censor any other info) and put a link here?
 
Upvote 0
What are you hoping to achieve by grouping it by Day ?
Based on what you have shown us, just remove the grouping (Click on any date > right click > UnGroup)
 
Upvote 0
I have worked it out and got it to work. My error - when I did text to columns and date - I removed the 'time' from the column and this allowed it to work. Appreciate all your responses - silly error which I realised this morning.
Have a lovely day all!
 
Upvote 0
Solution
I have an issue with pivot table not letting me group dates. I've formated, refreshed, deleted and re inserted pivot and can not get it to group date column. I've tried the text to column but it's reading the formula is the cell instead of the result. exhausted trying to figure this out.
 
Upvote 0

Forum statistics

Threads
1,226,839
Messages
6,193,266
Members
453,786
Latest member
ALMALV

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top