How to force nested if to format as date

[Data_things]

Hi folks,

I'm have a dataset of events in Airtable with each event as a row, and each row has a different number of sessions that can make up the complete event. This means I have 8 end date & time columns to capture the finish time of each session. I need a column that will dynamically show the end date & time of the whole event, regardless of the number of sessions so that I can use this in various calendar/timeline views in Airtable.

I've attempted to pull them all into an IF statement, like this:

IF({Session 8 end time},DATETIME_FORMAT({Session 8 end time},'DD/MM/YY h:mm A'),

IF({Session 7 end time},DATETIME_FORMAT({Session 7 end time},'DD/MM/YY h:mm A'),

IF({Session 6 end time},DATETIME_FORMAT({Session 6 end time},'DD/MM/YY h:mm A'),

IF({Session 5 end time},DATETIME_FORMAT({Session 5 end time},'DD/MM/YY h:mm A'),

IF({Session 4 end time},DATETIME_FORMAT({Session 4 end time},'DD/MM/YY h:mm A'),

IF({Session 3 end time},DATETIME_FORMAT({Session 3 end time},'DD/MM/YY h:mm A'),

IF({Session 2 end time},DATETIME_FORMAT({Session 2 end time},'DD/MM/YY h:mm A'),

IF({Session 1 end time},DATETIME_FORMAT({Session 1 end time},'DD/MM/YY h:mm A')))))))))

But I can't format the formula field as a date so it's recognised as an end date field for certain views. Can anyone point me in the right direction please? Thanks in advance!

[Andy_Lin1 · Accepted solution]

The DATETIME_FORMAT() function outputs a text representation of your date – so if you want Airtable to interpret your output as a date, simply remove the DATETIME_FORMAT() function! The fields you reference are already dates so they don't need to be specially formatted. You can change the display format by going into the settings for that field.

That being said, to address your original question, if you need to convert a date string to a date object that Airtable understands, use DATETIME_PARSE().

[ScottWorld]

Also, @Data_things, I would highly recommend restructuring your database. Instead of creating 8 redundant date fields for sessions, I would create an entirely new table for sessions and link each event to its sessions. Then, you could easily use rollup fields to get the first and last session. You also gain much more flexibility in many other ways as well.