DATETIME_PARSE as output of IF statement?

[Tom_R]

Hi all,

I’m trying to build a formula field that only displays the dates of records that have “Event”.

My formula is:

IF(FIND(“Event”,Name)>=1,DATETIME_PARSE(Date),"")

My FIND() condition works, but outputs the ISO date format of {date}. My goal is to output the format exactly as it is in {date}, a date object.

Anyone know what I’m missing from my formula?

E.g. 2020-02-25T00:00:00.000Z should be 25/2/2020.

Thanks all.

[kuovonne]

DATETIME_PARSE is for turning a string of text into a date object.
You want DATETIME_FORMAT which will turn a date object into readable text.

DATETIME_FORMAT({Date}, 'D-M-YYYY')

The DATESTR function will also turn a date object into readable text, but it will always be in YYYY-MM-DD format.

DATESTR({Date})

All these functions are documented in the formula field reference.

[Tom_R]

Sorry I should’ve specified, I would like the formula to return a date object, not a string object.

[kuovonne]

The {Date} field already is a date object. However, the way the date is stored internally is different from how it is displayed. Internally the date object is stored as the long string that you see in your formula.

Because Airtable knows the {Date} field is a date, Airtable can display it in a human readable format in the {Date} column.

However, because your formula can also return an empty string "", Airtable doesn’t know how to format the results of the formula, and just shows the date as it is stored internally.

Another option is to simplify the IF statement so that it does not return an empty string:

IF(FIND("Event",Name)>=1,Date)

[WilliamPorter]

I suggest that you try the DATETIME_FORMAT() function instead. Try this:

IF(
FIND(“Event”,Event),
DATETIME_FORMAT(Date,‘DD-MM-YYYY’),
BLANK()
)

I’m curious: Is the field Date displaying values in the format you gave an example of, i.e. “2020-02-25T00:00:00.000Z”? If so, do you need the time? If you don’t, make sure that the “Include a time field” option is toggled OFF (the default, I think) in the Customize Field dialog.

Make sense?

William