Conditional DateAdd formula not returning date value

[Karen_Sheffer]

Some help please:
I’m using an ‘estimated release date’ field with the following formulas to calculate for grid view, and show on a calendar view the duration of each project. Currently I’m getting a value not recognized as a date, and if I remove DATETIME_FORMAT I get a string with this message in the formatting tab “Your result type is not a number or a date.”

Start formula:
IF({Update Collateral (CS & Sales)}=‘No Need’,’ ------’,IF({Update Collateral (CS & Sales)},DATETIME_FORMAT(DATEADD({End: Update Collateral (CS & Sales)},-12,‘days’),‘DD/MM/YYYY’)))

End formula:
IF({Update Collateral (CS & Sales)}=‘No Need’,’ ------’,IF({Update Collateral (CS & Sales)},DATETIME_FORMAT(DATEADD({Estimated release},0,‘days’),‘MM/DD/YYYY’)))

And I’m trying to use european DD/MM/YYYY format but found the formula only works if I use MM/DD/YYY in the end formula.

What can be done?

[Julian_Kirkness]

Hi @Karen_Sheffer

Welcome to the community!

You may find it easier to calculate your field using the following formula:

IF({Update Collateral (CS & Sales)}!=“No need”,DATEADD({End: Update Collateral (CS & Sales)},-12,‘days’))

This returns either a value, which is a Date data type, or nothing. You are therefore able to use the field formatting option to display in the date format you want:

With your formula, the result could either be a string (the dashes) or a date and therefore you wouldn’t be able to use the field’s formatting feature.

Hope this helps!

[Karen_Sheffer]

Thanks, that’s in fact the formula I started out with but I was getting an invalid formual message - unfortunately still am using your formula as well.

What could be the problem?

[Julian_Kirkness]

Strange - not sure where to go here except is {Update Collateral (CS & Sales)} a date field?

[Karen_Sheffer]

Nope, it’s a multi-select field just as in the screenshot you included in your initial response.

[Jeremy_Oglesby]

Just a hunch, but I wonder if you are just suffering from Airtable’s formula editor not accepting “smart quotes”. If you copy/pasted Julian’s formula out of the forums and into Airtable, it would have retained the curled quotation marks, which are a different character that the quotation marks I have in the same formula below – try copy pasting this version into Airtable and see if that resolves the issue:

IF(
   {Update Collateral (CS & Sales)}!="No need",
   DATEADD(
      {End: Update Collateral (CS & Sales)},
      -12,
      'days'
   )
)

[Karen_Sheffer]

Jeremy_Oglesby:

IF( {Update Collateral (CS & Sales)}!=“No need”, DATEADD( {End: Update Collateral (CS & Sales)}, -12, ‘days’ ) )

Yes! That did it, thanks!
but now I get an error where the ‘update collateral (CS & Sales)’ field is empty, what’s my fix for that? (and preferably instead of leaving a blank space some kind of symbol or dashes)