Apr 11, 2019 10:19 PM
I’m going to try and explain this the best I can, and have provided a screenshot to help make sense out of my need for help.
I’m probably in way over my head, as I am learning formulas but I’m hoping for some help.
Here is what I’d like to happen:
(No zapier please…still learning)
Automatically add a check mark in followup box (hidden on example) if the ‘Status Change Date’ is over 14 days
Remove the #ERROR from the formula for those without dates. in the ‘Re’vd’ field.
Any help is greatly appreciated.
Apr 12, 2019 02:16 AM
You can wrap your Due formula in an error test to remove the errors. You can do this within the Due field itself. So replace Due in the formula below with whatever your formula is already.
IF(ISERROR(Due),BLANK(),Due)
For the follow up field use a formula field:
IF ( DATETIME_DIFF( TODAY(), {Status Change Date}, ‘days’) > 14, “ :white_check_mark: ”, BLANK() )
You can try different check marks by searching google for emojis.
I haven’t checked if these work, so there may be syntax errors.
Apr 12, 2019 06:44 AM
David -
I appreciate your help, but I’m not getting the either formula to work. I get the error code “Invalid Formula”.
Apr 12, 2019 07:19 AM
It might be because the quotation marks are the curly type rather than the usual ASCII format. It is quite annoying but this forum formats quotation marks into this curly format but Airtable doesn’t actually recognise them.
Apr 12, 2019 07:27 AM
I adjusted those when pasting
Apr 12, 2019 07:33 AM
Change BLANK() to “” in both. I keep forgetting that you can’t use BLANK() as an output.
Apr 12, 2019 07:52 AM
I don’t know if its something I’m doing wrong or what but here’s my formulas:
DATETIME_FORMAT(DATEADD({Re'vd}, 30, 'days'), "M-DD-YYYY"), IF(ISERROR(Due)"")
IF ( DATETIME_DIFF( TODAY(), {Status Change Date}, 'days') > 14, ":white_check_mark:","")
Apr 12, 2019 07:59 AM
The 2nd one should work. But it will display the literal string :white_check_mark: . You need to copy and paste the emoji you want from google.
I’m not sure what has happened with your first one. You should just be wrapping the error formula around your current Due formula:
IF( ISERROR( DATETIME_FORMAT(DATEADD({Re’vd}, 30, ‘days’), “M-DD-YYYY”) ),
“” , DATETIME_FORMAT(DATEADD({Re’vd}, 30, ‘days’), “M-DD-YYYY”))
I’m assuming that DATETIME_FORMAT(DATEADD({Re’vd}, 30, ‘days’), “M-DD-YYYY”) is your original Due formula.
You are testing to see whether the formula gives you an error. If it does, then you display an empty string there instead. If it doesn’t then you display the result of the formula.
Apr 12, 2019 08:16 AM
Yes!!! Finally got the 1st one right:
"", DATETIME_FORMAT(DATEADD({Re'vd}, 30, 'days'), "M-DD-YYYY"))
But still getting error message on this one
IF (DATETIME_DIFF( TODAY(),{Status Change Date}, 'days') > 14, "✔","")
Apr 12, 2019 03:28 PM
Do you have that space between the word IF
and the first opening paren (
in your formula in the editor? If so, that will throw an error…