Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Sorting a Date List with a Formula

Topic Labels: Dates & Timezones
851 6
cancel
Showing results for 
Search instead for 
Did you mean: 

I am having trouble sorting a list of records in chronological order by date using a field that I have formatted with a formula that calculates the day of the week, month, day of month. The sort is defaulting to alphabetical.

The formula I’m using is
CONCATENATE({Start Day},", ",{Start Date})

Start Day Formula is:
DATETIME_FORMAT({Start Date & TIme},‘dddd’)

Start Date formula is:
DATETIME_FORMAT({Start Date & TIme},‘ll’)

I’m running into an issue as I go from month to month that aren’t in alpha order (sept to oct for example is being listed with oct first. I feel that if I change the formula I sort by to a DATETIME_FORMAT then this could be fixed but I can’t figure it out. Any help or direction would be greatly appreciated!!

Thanks!!

6 Replies 6

Welcome to the Airtable community!

Is there a reason why you are not sorting on the original date/time field?

If you want to sort on a DATETIME_FORMAT result, it will sort alphabetically, so you need to format using numbers only (YYYY-MM-DD)

i’m not using the original date/time field because i want the day of the week to be included.

try ‘d-dddd’ behind your datetime format for start date

How do you want the day of the week to be included in the sort?

If you want the day of the week for display purposes, you can still sort on the original date field, and but just display the formula field with the day of the week. You can sort on a hidden field.

I’ve not had luck with sorting from a hidden field. Is this due to the fact that I have the records grouped. It seems that I am not allowed to sort by a field that is not a part of the grouping.

Further explanation:
I am grouping by “week #” field and then by “Start Day, Date” field.

“Week #” field is single select

“Start Day, Date” field. is:
CONCATENATE({Start Day},", ",{Start Date})

Grouping and sorting are different but related features. Both grouping and sorting can happen on hidden fields. If you use both grouping and sorting, the grouping happens first (with groups sorted according to the sort specified in the grouping), and then any additional sorting happens within the lows level of grouping.

image