Alphabetical sort of Dates

[CAHerrmann]

Hello,

I’m running into an issue with a calculated field. I have the following formula:

IF(
{Completion Date}=BLANK(),
IF(
{Contract Date}=BLANK(),
“Add Contract Date”,
DATETIME_FORMAT({Contract Date},‘MM-YYYY’)),
DATETIME_FORMAT({Completion Date},‘MM-YYYY’))

My issue is that, rather than sorting the date chronologically, Airtable is sorting it alphabetically. This happens with both MM-YYYY and MMM-YYYY formats (the only two I can use for this application).

For instance, with MM-YYYY, my sort looks like this:

01-2018
02-2017
03-2018
04-2018
05-2017

Similarly for MMM-YYYY (April, then August, etc)

How do I fix this? Do I need to wrap my calculation somehow?

[Elias_Gomez_Sai]

DATETIME_FORMAT returns a string, so the order is alphabetic. You have to wrap the formula with DATETIME_PARSE() to get back a date.

[CAHerrmann]

Awesome. Thanks you so much!

[CAHerrmann]

I tried it, but must be missing something.

Here is my formula. Can you advise on how to alter it?

IF(
{Completion Date}=BLANK(),
IF({Contract Date}=BLANK(),
“Add Contract Date”,
DATETIME_FORMAT({Contract Date},‘MM-YYYY’)),
DATETIME_FORMAT({Completion Date},‘MM-YYYY’))

Thanks,
Chris

[Jeremy_Oglesby]

Did you try wrapping the entire thing in DATETIME_PARSE()?

DATETIME_PARSE(
   << your original formula here >>,
  'MM-YYYY'
)

Although, reading your original post, I don’t think this is what you are after - this is going to give you a full date, with a day and all, I think.

[CAHerrmann]

Thanks, Jeremy.

Yea – not sure that will work for my formula as configured (due to the text alternate), nor for my date format requirements.

@Airtable_Team @Airtable_Support Any suggestions?

Also, out of curiosity, why would it be configured that a formula specifically made to configure a DateTime value would kick back a text value instead of a DateTime value? Seems counter-intuitive to me…

[Elias_Gomez_Sai]

CAHerrmann:

Add Contract Date

Because this is one of the possible results, that is a text string. You should remove it if you want to order by date.

[Jeremy_Oglesby]

@Elias_Gomez_Sainz has it right – one of the possible outputs of your formula is a “string” type value (plain text). Therefore, the entire field (column) is formatted as string so that it can accommodate that possibility. This isn’t just an Airtable convention, it’s a database convention, true across all databases. When a field is defined, the data type of that field has to be defined at the time of creation. When you enter a formula into a formula field, Airtable has to decide what data type that field will hold, and it can only choose one for defining that field. If any of the possible outputs of the formula is a string, then the field has to be of type “string” – it cannot be of type “date” or else it would not be capable of supporting the string output that your formula is capable of resolving to.

[CAHerrmann]

Ah, I see. Thank you both for the clarification. That makes a lot of sense.