May 06, 2019 07:29 AM
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?
May 06, 2019 10:44 AM
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!
May 06, 2019 11:49 PM
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?
May 07, 2019 01:54 AM
Strange - not sure where to go here except is {Update Collateral (CS & Sales)} a date field?
May 07, 2019 02:18 AM
Nope, it’s a multi-select field just as in the screenshot you included in your initial response.
May 07, 2019 09:37 AM
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'
)
)
May 12, 2019 06:46 AM
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)