Skip to main content
Solved

Grouping by date performed on first digits instead of as dates

0

Hi,

Somehow Airtable doesn't understand my dates as dates so grouping goes wrong. In an interface I have a list of booked events that I want to be grouped by their date. That way, I can easily see day by day how many events are booked. The grouping column is based on a formula picking out only the date from another column where the full date/time is stored.

As a European I prefer 'DD.MM.YYYY' as the format, so my formula to give only the date is:

DATETIME_FORMAT({date/time},'DD.MM.YYYY')

This gives me visually 27.5.2024 for May 27, 2024, which is what I want. 

The grouping result, however, becomes like this:

  • 02.06.2024
  • 14.06.2024
  • 22.05.2024
  • 28.5.2024
  • 31.5.2024

Grouping is apparently performed on the first digits, not as dates.

Why doesn't Airtable understand a date that is only formatted into DD.MM.YYYY as a date?

Rgds,

Björn

Best answer by kuovonne

When you use DATETIME_FORMAT(), the formula converts your date to a text string, so it is no longer considered a date by Airtable, even though it looks like one.

If your date field is a date-only field (no time), is there a reason you cannot group directly by the date field and set the date format to European? It is not exactly the same, as there will be slashes instead of dots. But the order will be the way you like.



If you want to use your existing formula field, you should be able to group by the original date field. If your original date field includes a time, you can use the formula DATESTR() to get a text string that will sort/group correctly with only the date. If you have timezone issues, you can nest SET_TIMEZONE() inside DATESTR().

 

View original
Did this topic help you find an answer to your question?

4 replies

S
  • Sho
  • Inspiring
  • 560 replies
  • May 27, 2024

Hi @0800-grizzly,

All non date types in date fields and formula fields are sorted as strings.
If you want to sort by European format, you need to use slash-separated dates in date fields

Alternatively, you could separate the fields for display and sorting.

kuovonne
Forum|alt.badge.img+17
  • kuovonne
  • Brainy
  • 5987 replies
  • Answer
  • May 28, 2024

When you use DATETIME_FORMAT(), the formula converts your date to a text string, so it is no longer considered a date by Airtable, even though it looks like one.

If your date field is a date-only field (no time), is there a reason you cannot group directly by the date field and set the date format to European? It is not exactly the same, as there will be slashes instead of dots. But the order will be the way you like.



If you want to use your existing formula field, you should be able to group by the original date field. If your original date field includes a time, you can use the formula DATESTR() to get a text string that will sort/group correctly with only the date. If you have timezone issues, you can nest SET_TIMEZONE() inside DATESTR().

 

0
1 person likes this
  • 0
    0800-grizzly
0
  • 0800-grizzly
  • Author
  • Known Participant
  • 58 replies
  • May 28, 2024
kuovonne wrote:

When you use DATETIME_FORMAT(), the formula converts your date to a text string, so it is no longer considered a date by Airtable, even though it looks like one.

If your date field is a date-only field (no time), is there a reason you cannot group directly by the date field and set the date format to European? It is not exactly the same, as there will be slashes instead of dots. But the order will be the way you like.



If you want to use your existing formula field, you should be able to group by the original date field. If your original date field includes a time, you can use the formula DATESTR() to get a text string that will sort/group correctly with only the date. If you have timezone issues, you can nest SET_TIMEZONE() inside DATESTR().

 


Thank you!

I had no clue DATETIME_FORMAT makes the date a string instead, thought it was just a way to format any dates. Need to look through the rest of my stuff to find other instances of this 🙂.

Unfortunately my original date-field includes time, and needs to stay that way. Would be handy to be able to group/sort on only date, although there's time, but that's for the wish list.

While DATESTR indeed works for grouping, the ISO format of YYYY-MM-DD is very un-intuitive for us here in the Nordics.

I also tried DATETIME_PARSE (+ format and/or locale) to see if that could be tricked into formatting, but apparently it won't parse a true date-field.

For now I settled for a cheap workaround. I just "echo" the original date-field in a new column (just the column name as the formula), and have its formatting set to "European".

But sorting this so that we can use a DD.MM.YYYY format would make it so more intuitive for quick eyes used to read dates this way 🙂. Any further thoughts on this, other than ask Airtable to provide it as a native format?

Rgds,

Björn

0
  • 0800-grizzly
  • Author
  • Known Participant
  • 58 replies
  • May 28, 2024
Sho wrote:

Hi @0800-grizzly,

All non date types in date fields and formula fields are sorted as strings.
If you want to sort by European format, you need to use slash-separated dates in date fields

Alternatively, you could separate the fields for display and sorting.


Thanks!

Björn

Reply


Most helpful members this week

Terms & Conditions