Nested If (and) with only date Fields

Topic Labels: Formulas
Solved
1114 4
cancel
Showing results for
Did you mean:
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()
),
{Date Item Added to Air Table}, 35, ‘days’
),
IF(
{Item Start Date Manual Override} != BLANK(),
{Item Start Date Manual Override}, 35, ‘days’
),
{Last Keyword Insertion Date}, 35, ‘days’
)
)
)

I have also tried:

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

1 Solution

Accepted Solutions
18 - Pluto

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

`{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.

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

Use this:

`{Item Start Date Manual Override}`

4 Replies 4
18 - Pluto

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

`{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.

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

Use this:

`{Item Start Date Manual Override}`

6 - Interface Innovator

Thanks so much! That was exactly what i needed!

18 - Pluto

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

18 - Pluto

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"
)
``````