Auto update and error field

#1

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.

Imgur

0 Likes

#2

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.

1 Like

#3

David -
I appreciate your help, but I’m not getting the either formula to work. I get the error code “Invalid Formula”.

0 Likes

#4

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.

1 Like

#5

I adjusted those when pasting

0 Likes

#6

Change BLANK() to “” in both. I keep forgetting that you can’t use BLANK() as an output.

1 Like

#7

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:","")
0 Likes

#8

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.

1 Like

#9

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, "✔","")
0 Likes

#10

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…

1 Like

#11

That was it Jeremy! :man_facepalming:

1 Like