Report Date Sorting by Number Instead

jlee579

Hello - I have date format sheet summary fields rollling up to a sheet summary report and when I sort on the date column it sorts things by numeric value instead by date values. The sort selections are Newest to Oldest and Oldest to Newest so the filter knows the column/field formatting involves dates/age. Thoughts?

Sorts as 9/30/24, 9/22/24, 9/10/23, then 9/4/24 when it should sort as 9/30/24, 9/22/24, 9/4/24, 9/10/23.

Paul Newcome

In that case you would need to either set [Column4] as a date type column, or you would need to adjust the formula in the Sheet Summary field to output a date type value.

=DATE(VALUE("20" + RIGHT([Column4]15, 2)), VALUE(LEFT([Column4]15, FIND("/", [Column4]15) - 1)), VALUE(MID([Column4]15, FIND("/", [Column4]15) + 1, FIND("/", [Column4]15, FIND("/", [Column4]15) + 1) - (FIND("/", [Column4]15) + 1))))

kowal

hi @jlee579,

It seems that your column 1st Ob Ships is not a Date column but text that's why it does not consider it as dates but text.

can you confirm it's a date type column?

jlee579

Are you referring to the column on the report or the Sheet?

Paul Newcome

What is the formula you are using to populate the Sheet Summary field?

jlee579

I am using a basic 'equals' formula from sheet to the Summary Fields.

=[Column4]15

Paul Newcome

In that case you would need to either set [Column4] as a date type column, or you would need to adjust the formula in the Sheet Summary field to output a date type value.

=DATE(VALUE("20" + RIGHT([Column4]15, 2)), VALUE(LEFT([Column4]15, FIND("/", [Column4]15) - 1)), VALUE(MID([Column4]15, FIND("/", [Column4]15) + 1, FIND("/", [Column4]15, FIND("/", [Column4]15) + 1) - (FIND("/", [Column4]15) + 1))))

jlee579

Thank you Mr. Newcome that worked !! I appreciate everyone's fast responses and support.

Paul Newcome

Happy to help. 👍️