Help

Re: IF formula questions

857 1
cancel
Showing results for 
Search instead for 
Did you mean: 
D_Wang
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi, I would like to construct the following statement:
If {check 1} is filled in with a date, then return the value of 10 days after the date of check 1
If {check 1} is blank, then leave the current cell blank

The following is what I’ve tried, would appreciate some suggestions.

IF({check 1} is not blank,(DATEADD{check 1},10,'date), ())

Thanks in advance!

6 Replies 6

Try

IF(
    {Check 1},
    DATEADD(
        {Check 1},
        10,
        'days'
        ),
    BLANK()
    )

Or, if you prefer a non-indented version:

IF({Check 1},DATEADD({Check 1},10,'days'),BLANK())

(Either can be copy-and-pasted into your formula field.)

You don’t need to declare explicitly {Check 1}!=BLANK(), as IF({Check 1}… contains an implied ‘is not a blank|empty|null’ value. I seem to recall instances where an explicit comparison ran afoul; for instance, when Field='' (that is, an empty string), IF(Field=BLANK() might confusingly evaluate to FALSE under some circumstances, but TRUE under others. (Admittedly, other of my mistakes may have caused the error — but the abbreviated version always evaluates as one expects.)

Similarly, strictly speaking, the final BLANK() isn’t necessary; you could delete it and the comma after DATEADD(). I go back and forth on this question; lately, though, I’ve tended to leave it in, mainly because, 10 months from now, should I have reason to revisit the code, it will take me a few seconds less to figure out what it does.

And make sure your unit specifier is 'days' or 'd'. DATEADD() might match the ‘d’ in 'date' and be happy; then, again, it might not.

D_Wang
5 - Automation Enthusiast
5 - Automation Enthusiast

@W_Vann_Hall

Thank you SO MUCH!!!
It worked beautifully, and I appreciate the explanation - I feel feel a bit smarter now :slightly_smiling_face:

Amy_Asch
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello from a beginner – I have a situation where if Amy is the editor, the {Amy Needs To Approve} field should show 1 workday after the {Upload Date} field, but IF Amy is not the editor the {Amy Needs To Approve} field should stay blank. Using the post above as my template, I came up with the following, but am now getting blanks in all records. I would so appreciate your help with tweaks!

IF(
{Editor}=“Amy”,
WORKDAY({UploadDate},1),
BLANK( )
)

It sounds like either your condition is always evaluating as false, or else your WORKDAY() formula is producing blanks.

Check:

  1. That {Editor} = "Amy" is capable of evaluating as true; do this by creating a separate, throw-away formula field and just typing that as the formula to see the result – adjust it until you get it properly returning 1 when "Amy" is found in that field, and 0 when it’s not.
  2. Your formatting of the field name in WORKDAY({UploadDate}, 1) is correct – in your description you typed the field name as {Upload Date}; make sure that discrepancy isn’t causing errors in your formula

@Jeremy_Oglesby – Thank you so much!

Such a great tip to try out each section of the formula separately.

And it did turn out to be a matter of accurate nomenclature. Not in whether the field name was 1 word or 2 (somehow I’d gotten that right), but in how I’d populated the records. There were no fields where Editor=“Amy” because the field content actually said “Amy Asch.” Duh!

I’m curious. Is there a way to set up something like IF field contains/does not contain, rather than IF Field equals?

Thanks again!!

IF(
   FIND("Amy", {Editor}),
   WORKDAY({Upload Date}, 1)
)