Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Help please. Converting text field with mix of partial dates and friendly dates to ISO date format

Topic Labels: Formulas
262 1
cancel
Showing results for 
Search instead for 
Did you mean: 

partial-date-parsing
Hi, I’m importing data that have “Publish Dates” in a text field.
Most of them are just Month Year (April 2021) some of them are complete (April 30, 2021).
I would like to use the formula field to convert them all to YYYY-MM-DD and use them in calendar/gantt view. It should always default to the 1st of the month for a missing day (i.e. April 2021 will be converted to 2021-04-01)

I tried to search for a way how to use IFs and DATETIME_PARSE but I couldn’t make it work :man_shrugging:

1 Reply 1

This should work:

IF({source}, DATETIME_FORMAT(DATETIME_PARSE({source}), "YYYY-MM-DD"))

Substitute {source} for the name of your source field.

You can remove the IF statement if you wish, but leaving it in will cause the formula to yield a blank field instead of an error if the input field is also blank.