Help with nested IF statements that check dates


#1

I’m trying to extend a function that I previously got some help with on this forum.

My use case is a base for storing details on art exhibitions, with fields for a start and end date for each exhibition.

With help, I got the formula working when it was only checking 3 conditions - Has it started? Is it currently running? Has it finished?

I’ve now tried to nest another IF to test whether the exhibition will end soon (a certain number of days to the End_date), but I can’t get it to work. I’m wondering whether it’s even possible to do what I want!

The closest I’ve got are these two. The following will flag all currently open exhibitions as “Ending soon” even if they’re not.

IF(IS_AFTER({Start_date},TODAY()),
“Not yet started”,

IF(DATETIME_DIFF(TODAY(),{End_date},‘days’)<=3,
“Ending soon”,

IF(IS_AFTER({End_date},TODAY()),
“Currently running”,
“Already finished”)))

The following will flag all open exhibitions as “Currently running” even if they’re due to finish within 3 days.

IF(IS_AFTER({Start_date},TODAY()),
“Not yet started”,

IF(IS_AFTER({End_date},TODAY()),
“Currently running”,

IF(DATETIME_DIFF(TODAY(),{End_date},‘days’)<=3,
“Ending soon”,
“Already finished”)))


#2

First, you’ll want to use [a slightly corrected version of] your first formula. IF() statements stop processing after encountering a test that evaluates to TRUE. Since even shows that end in 3 days are also ‘currently running’, your second statement will never get to the ‘less than 3 days’ test.

The problem with your first formula is that you’ve reversed the variables in DATETIME_DIFF(). I inevitably do, too, as there no semantic reason for one to know the function works by subtracting Date2 from Date1, and not vice-versa. In the order you have them listed, if the show has not yet closed, DATETIME_DIFF() will always return a negative number — which is, of course, less than 3.

Hmmm… actually, that formula needs a little more work than simply reversing the variables. Originally I was going to say you should use this revised version — but don’t.

Do you see why?

IF(
    IS_AFTER(
        {Start_date},
        TODAY()
        ),
    'Not yet started',
    IF(
        DATETIME_DIFF(
            {End_date},
            TODAY(),
            'days'
            )<=3,
        'Ending soon',
        IF(
            IS_AFTER(
                {End_date},
                TODAY()
                ),
            'Currently running',
            'Already finished'
            )
        )
    )
                'DO NOT USE!'

The problem is still there; only the conditions are reversed. Now, once the show closes, the DATETIME_DIFF() clause will start reporting it ends soon. We need to rearrange the order of the tests so the IF() statement will stop evaluating earlier.

I think this will do what you want:

IF(
    IS_AFTER(
        {Start_date},
        TODAY()
        ),
    'Not yet started',
    IF(
        IS_AFTER(
            TODAY(),
            {End_date}
            ),
        'Already finished'
        IF(
            DATETIME_DIFF(
                {End_date},
                TODAY(),
                'days'
                )<=3,
            'Ending soon',
            'Currently running'
            )
        )
    )

#3

Thank you very much, especially for the explanation.

Just one minor correction needed - a comma after ‘Already finished’, then it was perfect.

IF(
IS_AFTER(
{Start_date},
TODAY()
),
“Not yet started”,
IF(
IS_AFTER(
TODAY(),
{End_date}
),
“Already finished”,
IF(
DATETIME_DIFF(
{End_date},
TODAY(),
‘days’
)<=3,
“Ending soon”,
“Currently running”
)
)
)


#4

Good catch! (I was feeling lazy and tried to get by without going through the hassle of creating a dummy table in my ‘scratch’ base with appropriately named and configured fields for testing. And, as always, proved once again why I shouldn’t.)