Nested Formula Overlaps

[Alex_Aguilar]

IF(AND({Days Until Event}<=7, {Hours From Last Check}<-36), “yes”, IF(AND({Days Until Event}>7, {Hours From Last Check}<-71), “yes”, IF(AND({Days Until Event}>31, {Hours From Last Check}<-160), “yes”, “check”)))

Wondering if there is a quick fix to this formula or it needs to be redone with a different approach. The problem i’m having is an overlap between the two nested IF/AND formulas containing >7/>31. It is returning a yes for events over 31 days that have been checked >-160, and i want it to return a check for events over >31 <-160. I know this is due to the previous nested IF statement of >7/<-71.

[W_Vann_Hall]

I’d probably go with

IF(
    {Days Until Event}>31,
    IF(
        {Hours From Last Check}<-160,
        "yes",
        "check"
        ),
    IF(
        {Days Until Event}>7,
        IF(
            {Hours From Last Check}<-71,
            "yes",
            "check"            
            ),
        IF(
            {Days Until Event}<=7,
            IF(
                {Hours From Last Check}<-36,
                "yes",
                "check"
                ),
            "check"
            )
        )
    )

(I think that evaluates as you intended.)

There may be some clever ways to short-circuit it, thus eliminating redundant "check"s, but maintainability is perhaps more important than conciseness in this instance.