Help

Nested If (and) with only date Fields

Topic Labels: Formulas
Solved
Jump to Solution
989 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Admin_Expert
6 - Interface Innovator
6 - Interface Innovator

I’ve been pouring through the other IF threads, and still can’t find a solution that returns the correct results. Instead, I’m getting #ERROR.

Here is what I want to do:
I need to set a date in the “Next Update Due” field. It should be 35 days from “X” date. I need the {Item Start Date Manual Override} field to have priority over the others, unless {Item Start Date Manual Override} is blank.

SO:

If {Item Start Date Manual Override} is not blank, then i want to add 35 days to {Item Start Date Manual Override}
otherwise
If {Last Keyword Insertion Date} is not blank, then i want to add 35 days to {Last Keyword Insertion Date}
otherwise
i want to add 35 days to {Date Item Added to Air Table}

Here are the two versions of the formula as I’ve written them, but it gives me an error unless there is a value in the {Item Start Date Manual Override} field

IF(
and(
{Last Keyword Insertion Date} = BLANK(),
{Item Start Date Manual Override} = BLANK()
),
DATEADD(
{Date Item Added to Air Table}, 35, ‘days’
),
IF(
{Item Start Date Manual Override} != BLANK(),
DATEADD(
{Item Start Date Manual Override}, 35, ‘days’
),
DATEADD(
{Last Keyword Insertion Date}, 35, ‘days’
)
)
)

I have also tried:

If(
{Item Start Date Manual Override} != BLANK(),
DATEADD(
{Item Start Date Manual Override}, 35, ‘days’
),
IF(
{Last Keyword Insertion Date} != BLANK(),
DATEADD(
{Last Keyword Insertion Date}, 35, ‘days’
),
DATEADD(
{Date Item Added to Air Table}, 35, ‘days’
)
)
)

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

Don’t use BLANK() to evaluate if the field is empty. Just use = "" to see if the field is empty.

So instead of:

{Item Start Date Manual Override} = BLANK()

Use this:

{Item Start Date Manual Override} = ""

If you’re trying to find out if the field has something in it, just use the field name itself.

So, instead of:

{Item Start Date Manual Override} != BLANK()

Use this:

{Item Start Date Manual Override}

See Solution in Thread

4 Replies 4
ScottWorld
18 - Pluto
18 - Pluto

Don’t use BLANK() to evaluate if the field is empty. Just use = "" to see if the field is empty.

So instead of:

{Item Start Date Manual Override} = BLANK()

Use this:

{Item Start Date Manual Override} = ""

If you’re trying to find out if the field has something in it, just use the field name itself.

So, instead of:

{Item Start Date Manual Override} != BLANK()

Use this:

{Item Start Date Manual Override}

Thanks so much! That was exactly what i needed!

You’re welcome! Glad I could help! :slightly_smiling_face:

Another way to check for a blank field is to invert the check for a non-blank field using NOT():

NOT({Item Start Date Manual Override})

On another note, the entire formula could be greatly simplified. The DATEADD() function is going to be run no matter what, so instead of putting the IF() function on the outside, put it on the inside, returning only the date needed for the calculation:

DATEADD(
    IF(
        {Item Start Date Manual Override},
        {Item Start Date Manual Override},
        IF(
            {Last Keyword Insertion Date},
            {Last Keyword Insertion Date},
            {Date Item Added to Air Table}
        )
    ) , 35, "days"
)