Skip to main content

Sorting a Date List with a Formula

  • August 8, 2021
  • 6 replies
  • 152 views
M
Forum|alt.badge.img+4

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

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • August 8, 2021

    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)

    M
    Forum|alt.badge.img+4
    • Melanie_Lemnios
    • Author
    • New Participant
    • 4 replies
    • August 9, 2021

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

    R
    Forum|alt.badge.img+4
    • Rebecca_Elam
    • Inspiring
    • 150 replies
    • August 9, 2021

    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

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • August 10, 2021

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


    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.

    M
    Forum|alt.badge.img+4
    • Melanie_Lemnios
    • Author
    • New Participant
    • 4 replies
    • August 10, 2021

    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})

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • August 10, 2021

    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.


    Terms & ConditionsAccessibility statement