Pivot chart doesnt sort custom date correctly

[Guinaba]

Hi experts,

I need the following format on my date "dd/mm/yyyy ddd" (31/12/2021 Sun) so I am using the format function on powerpivot to get the date in that format, however when I do that, my chart doesn't sort the date correctly. See pic below


Incorrect sorting using custom date format:

Correct sorting using standard date format:

 

[bobsan42]

Check if the field/axis on the chart is marked as text rather than date.
It seems to be sorted as text.

 

[Guinaba]

Check if the field/axis on the chart is marked as text rather than date.
It seems to be sorted as text.

Hi @bobsan42,

Correct is marked as text, that is the issue, however not sure, how to add that format and keeping the field as date.

 

[bobsan42]

Using the format function you have turned your date data into strings (text). Excel offers plenty of formatting options so don't be too quick to get rid of the native data.
To keep the dates ditch the format function, but use the Custom Number format option.
Formatting the date like this will be something like: dd/mm/yyyy ddd
The other option if you have to use string data is to format it in the reverse order: yyyy/mm/dd - then at least the sorting will be correct.

 

[Guinaba]

Using the format function you have turned your date data into strings (text). Excel offers plenty of formatting options so don't be too quick to get rid of the native data.
To keep the dates ditch the format function, but use the Custom Number format option.
Formatting the date like this will be something like: dd/mm/yyyy ddd
The other option if you have to use string data is to format it in the reverse order: yyyy/mm/dd - then at least the sorting will be correct.

@bobsan42 I did that in PowerPivot (pic below), however not sure why the chart is not picking up the format, neither the slices.

 

[bobsan42]

Did you change the field name. Try to rebuild the chart. And check again the date/text axis property.

 

[Guinaba]

Hi @bobsan42, I recreated the chart and pivot, I can see the date ok on the table but in the chart, maybe there is a limitation on the chat to display the same format.

 

[bobsan42]

You can define your custom number format for the axis numbers/labels of the chart.
However for this to actually work you have to turn OFF Date groupint for the row labels (dates) of the pivot table/chart. Otherwise the dates are converted to text.

Check this article:
How to Change Date Formatting for Grouped Pivot Table Fields - Excel Campus
and specifically the section Automatic Date Field Grouping.

 

[Guinaba]

Thanks @bobsan42! That's really a good article, I'll try some of the options and see how it goes. I'll let you know.

 

[Guinaba]

Hi @bobsan42,

Have found an easy way to do it sorting the text column 31/12/2022-Mon by the date column link below.

Excelguru Help Site - Sorting A Column Of PowerPivot Data By Another Column