Pivot table date format

[kristell]

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!

 

[etaf]

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

 

[RoryA]

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.

 

[kristell]

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

 

[kristell]

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

 

[RoryA]

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?

 

[Alex Blakenburg]

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)

 

[kristell]

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!

 

[Alex Blakenburg]

Thanks for letting us know. Glad you were able to get it working.

 

[pnewkirk12]

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.