Date format for formula not formatted correctly and cannot change format

Question

I created a formula to add 3 weeks to a date that I capture elsewhere in my table, and added an IF blank statement, so if there is no date it won’t show anything:
IF({Date Intake Completed}=BLANK(),0,DATEADD({Date Intake Completed},21,‘days’))
The formula is working, but the output format is not what I’d like:  2021-05-19T00:00:00.000Z, I would prefer 05-19-2021, or something like that.
When I try to change the format though, I get this message: Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.
The output is a date, so I am not sure why I am not given the option to update the format.
Appreciate any help!  I am new to Airtable and this is my 1st formula, so hoping I can get it working.

Chris-T · Accepted Answer

Hi Laurence. The problem is that your IF statement can return a 0 or a date. All you have to do is rearrange your logic in the IF like

IF({Date Intake Completed}, DATEADD({Date Intake Completed},21,‘days’))

This formula says that if {Date Intake Completed} is not empty, then return your new date, otherwise return nothing. This should allow you to go into the formatting tab and do away with the time part of the date/time.

Good luck!

That worked, thank you!

Laurence_Lagae · Answer

Hi Laurence. The problem is that your IF statement can return a 0 or a date. All you have to do is rearrange your logic in the IF like

IF({Date Intake Completed}, DATEADD({Date Intake Completed},21,‘days’))

This formula says that if {Date Intake Completed} is not empty, then return your new date, otherwise return nothing. This should allow you to go into the formatting tab and do away with the time part of the date/time.

Good luck!

That worked, thank you!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded