Report Date Sorting - Out of Order

Options

Some of my report dates are sorting out of order, does anyone know why?

Master Sheet 1: Dates in a column, both 2024 and 2025 data; Column Type = Date; All reports sort correctly

Master Sheet 2: Dates in a column, populated by JOIN(COLLECT), cell formula; Column Type = Date;

Master Sheet 2, Related Reports: Sort by "Store Open Date", Oldest to Newest; Reports have 2 rows/lines that will not sort with other 2025 projects. Checked date fields for errors, both contain true dates.

On all of my reports, they are sorted by year, then month, then day. This seems to be only sorting by month and not considering the year. Is this due to the Join/Collect formula? Any way around this?

Why won't they go with their friends??

Tags:

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭
    edited 06/10/24
    Options

    With the JOIN(COLLECT( formula, are you using a delineator of some sort to populate more than one date in a single cell? If you are just looking to pull in one date, I would suggest using an INDEX(COLLECT( formula instead. It's possible your report is having trouble sorting because there are actually multiple dates in a date cell or some other reason.

    If you also want to share screenshots of the grouping and sorting settings on your report, that may be where the issue is as well. Happy to dive in further!:)

  • Brian_Richardson
    Brian_Richardson Overachievers
    Options

    Yes, it's because JOIN turns them into text, not dates. So it's going strictly in text order by first characters. If you had 1 instead of 01 for the month you'd also notice it goes 1,10,11,12,2,3,4,5,6,7,8,9

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN