Skip to main content
Solved

Nested If (and) with only date Fields

A
Forum|alt.badge.img+7

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’
)
)
)

Best answer by ScottWorld

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}

View original
Did this topic help you find an answer to your question?

4 replies

ScottWorld
Forum|alt.badge.img+33
  • ScottWorld
  • Brainy
  • 8778 replies
  • Answer
  • June 19, 2020

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}

A
1 person likes this
  • A
    Admin_Expert
A
Forum|alt.badge.img+7
  • Admin_Expert
  • Author
  • Participating Frequently
  • 7 replies
  • June 19, 2020
ScottWorld wrote:

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!

ScottWorld
1 person likes this
  • ScottWorld
    ScottWorld
ScottWorld
Forum|alt.badge.img+33
  • ScottWorld
  • Brainy
  • 8778 replies
  • June 19, 2020
Admin_Expert wrote:

Thanks so much! That was exactly what i needed!


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

Justin_Barrett
Forum|alt.badge.img+20

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"
)
ScottWorld
A
2 people like this
  • ScottWorld
    ScottWorld
  • A
    Admin_Expert

Reply


Most helpful members this week

Terms & Conditions