Pivot chart doesnt sort custom date correctly

[Anonymous]

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

Correct sorting using standard date format

Incorrect sorting using formatted date

Any suggestion? 

[Greg_Deckler]

@Anonymous That's because using FORMAT changes the data to Text and not Date. You will need a Sort By column most likely or try using custom format strings. See Chelsie Eiden's Duration. Chelsie Eiden's Duration - Microsoft Power BI Community

[Anonymous]

Thanks @Greg_Deckler , however not sure how I could adjust your suggestion (link) from hours to days. I have tried to change the date format, still using the date format, but not sure why Excel doesn't recognize it. Using Sort by column doesn't work either. I am not using powerbi, just excel.

[Anonymous]

Hi @Anonymous ,

The line chart can sort by the date field correctly even though set the format of the date field as "dd/mm/yyyy ddd" just as shown in below screenshot...

In addition, please review the content in the following links, hope they can help you resolve the problem.

Excel Won't Sort Dates Correctly - The Solution!

How to sort by date in Excel: in chronological order, by month, auto sort

Best Regards

[Anonymous · Accepted solution]

Thanks @Anonymous , I ended up solving the problem sorting out the column based on another col. which was date type