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