Skip to main content

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?

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!

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!


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?

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?


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

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


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

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


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'

   )

)

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'

   )

)


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)

Reply


Terms & Conditions