Format date fields

[Kongtho_Imarith]

Hi everyone, would you please tell me how to convert a multiple date string field (i.e., 2/11/2024, 3/3/2024, 2/19/2024, 2/25/2024) to the correct format date field that reflects all the dates in the string field? Please note that the string field mentioned contains the date values based on a lookup function from its source field or table. Thank you.

[TheTimeSavingCo]

You could create a super long, complicated formula that'll split out the date strings for you, parse them into dates, and then format them?  It'd be hardcoded and really tedious though

Instead of that, can we just create a formula field in the original table to display that date field, format it the way we want, and then point the lookup field at that?

[dilipborad]

Hello @Kongtho_Imarith 

Airtable's structure doesn't natively support multiple dates in a single field, here's a breakdown of solutions focusing on code examples, considering it's an Airtable-related question.

Assumptions:

• Your multiple dates field is named "Multiple Dates".
• Dates are formatted as "MM/DD/YYYY" and separated by commas.

Prioritizing Code with Examples

1. Formula Field Approach (Limited Flexibility)

• Code: DATETIME_PARSE(MID("Multiple Dates", FIND(",", "Multiple Dates") + 2, 10), 'MM/DD/YYYY')
• Explanation: Extracts only the first valid date from the string. You'd need multiple formula fields with adjusted formulas to extract subsequent dates.
• Example: If "Multiple Dates" contains "2/11/2024, 3/3/2024, 2/19/2024, 2/25/2024", this formula would output a date field with the value 2024-02-11.

2. Formula + Rollup (Handles Multiple Dates)

• Formula Field Code ('Extracted Date'):

  DATETIME_PARSE(MID(ARRAYJOIN(SPLIT("Multiple Dates", ", ")), 1, 10), 'MM/DD/YYYY')

• Rollup Field Code ('All Dates'):

  ARRAYJOIN(ARRAYUNIQUE(values), ", ")

• Explanation:

  • The formula field extracts individual dates as an array.
  • The rollup field collects the extracted dates and presents them as a comma-separated list.

Caveats of the Formula-Based Approach

• Limited direct manipulation of the extracted dates (e.g., sorting, filtering)
• Potential complexity with inconsistent date formatting or a large number of dates.

3. Scripting or Automation (Advanced)

For highly dynamic scenarios or complex date operations, you would likely need a script (JavaScript in Airtable) or an automation solution. This would provide the most flexibility in terms of restructuring and manipulating the date data.

Key Choice: Flexibility vs. Complexity

• If you need to perform calculations, sorting, or extensive filtering on individual dates, restructuring your Airtable (using linked records as explained earlier) is the most robust long-term solution.
• If you require a quick workaround and immediate display of multiple dates, the formula and rollup approach might suffice.

I hope you like it.
👍

[Kongtho_Imarith]

Hi @dilipborad 

Many thanks for your solutions to my challenges. I am interested in solution 2 (Formula + Rollup (Handles Multiple Dates). But when I adopted it, the formula code below could not know the SPLIT function when I saved it. The message says, "Sorry, there was a problem saving this field. Unknown function names: SPLIT". 

DATETIME_PARSE(MID(ARRAYJOIN(SPLIT("Multiple Dates", ", ")), 1, 10), 'MM/DD/YYYY')

Would you please tell me further on how to make this work? 

Thank you.