Skip to main content

Tricky nested IF using multiple fields

  • February 2, 2022
  • 1 reply
  • 16 views
K
Forum|alt.badge.img+3
  • kts
  • Participating Frequently

hey community, I’m working on this formula to return a simple “Y” or “N” based on whether or not a date is past. The items in the grid could either be on a single date, or could have both a start date and an end date. I’ve tried a couple different ways of writing this formula but getting a lot of errors.

What I’d like to do is: “if the end date field is empty, determine whether or not the event is in the past based on the start date field; otherwise [if the end date field has a date], determine whether or not the event is in the past based on the end date field.”

Here’s what I’ve got:

IF({End Date} = "", IF(IS_AFTER(TODAY(), {Start Date}), "Y", "N")), IF(IS_AFTER(TODAY(), {End Date}, "Y", "N")))

Could somebody help me figure out what I’m doing wrong? Thank you!

1 reply

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • February 8, 2022

This is kinda an old topic, but in case you weren’t able to resolve this one…

Your formula’s intended flow is:

  • If the end date is blank:
    • If the start date is after today, then return “Y”. Otherwise, return “N”.
  • If the end date is not blank:
    • If the end date is after today, then return “Y”. Otherwise, return “N”.

With this in mind, here’s the fixed formula.

IF(
    {End Date} = "",
    IF(
        IS_AFTER(
            TODAY(), {Start Date}
        ),
        "Y",
        "N"
    ),
    IF(
        IS_AFTER(
            TODAY(), {End Date}
        ),
        "Y",
        "N"
    )
)

You had a few syntax errors that were causing the error returns.

Here’s a breakdown of the errors in case you’re curious:

1 IF(
2    {End Date} = "",
3    IF(
4       IS_AFTER(
5           TODAY(), {Start Date}
6       ),
7       "Y",
8       "N"
9   )
10 ), 
11
12 IF(
13     IS_AFTER(
15         TODAY(), {End Date},
16     "Y",
17     "N"
18     )
19 )

Here’s your original formula field in a formatted view.

The first issue is on lines 9 & 10.
The parenthesis on line 10 closes the first IF statement. This means that if your first IF statement returns false, then it is returning blank, as there is not a declared argument parameter for the FALSE part of the IF formula syntax.

Thus, everything after the parenthesis on line 10 is considered another formula altogether.

That means that the closing parenthesis included on line 19 is just free-floating and isn’t attached to a statement. This would also be a source of a syntax error.

If you were to remove the comma on lines 10 & 19, and added an ‘&’ between the two statements, you would just have two separate IF statements that would return a combination of two values or just blank values depending on the input of the End Date and/or the Start Date.

Apologies for the theoretical exploration lol.

K
Terms & ConditionsAccessibility statement