Help with nested IF statements that check dates

[Greg_Gillet]

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

[W_Vann_Hall]

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

[Greg_Gillet]

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

[W_Vann_Hall]

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

[Matthew_Sulliva]

Hey @W_Vann_Hall!

I came across this thread while trying to add a “due date is getting close” emoji to a formula of mine. I think it may be ordering or using the wrong function, but it’s returning blank if not paid. Was wondering if you had any insight. Your forum posts are always incredibly helpful. Here’s what I’ve got.

IF(

Chore=1,CONCATENATE(" :clap:t2: “,Roommate,” " & {What was it?}),

IF(AND(

Chore=0,Paid=1,IS_AFTER({Date},TODAY())),CONCATENATE(“ :money_with_wings: “,Roommate,” " & DATETIME_FORMAT(Date, ‘MMMM’),” ",{Bill Name}),

IF(AND(

Chore=0,Paid=1,IS_SAME({Date},TODAY())),CONCATENATE(“ :money_with_wings: “,Roommate,” " & DATETIME_FORMAT(Date, ‘MMMM’),” ",{Bill Name}),

IF(AND(

Chore=0,Paid=1,IS_BEFORE({Date},TODAY())),CONCATENATE(“ :money_with_wings: “,Roommate,” " & DATETIME_FORMAT(Date, ‘MMMM’),” ",{Bill Name}),

IF(AND(

Chore=0,IS_SAME({Date},TODAY())),CONCATENATE(“ :radioactive: “,Roommate,” " & DATETIME_FORMAT(Date, ‘MMMM’),” ",{Bill Name}),

IF(AND(

Chore=0,IS_AFTER(TODAY(),{Date}),CONCATENATE(“ :rotating_light: “,Roommate,” " & DATETIME_FORMAT(Date, ‘MMMM’),” ",{Bill Name}),

IF(AND(

Chore=0,IS_AFTER({Date},TODAY())),CONCATENATE(“ :grinning_face_with_big_eyes: “,Roommate,” " & DATETIME_FORMAT(Date, ‘MMMM’),” ",{Bill Name}),

IF(AND(

Chore=0,DATETIME_DIFF({Date},TODAY(),‘days’)<=5,CONCATENATE(“ :grimacing: “,Roommate,” " & DATETIME_FORMAT(Date, ‘MMMM’),” ",{Bill Name})

)))))))))

Any help would be greatly appreciated. Thank you so much!!

[W_Vann_Hall]

You’ve undoubtedly discovered this by now (I’m finally clearing up my backlog of posts and messages), but you’ve dropped the closing parenthesis from two of your AND() statements — namely, the ones preceding :rotating_light: and :grimacing: . That pulls your CONCATENATE() functions into the AND() and skews your logic.

Not that it’s a problem, but you’re actually using two different forms of concatenation in those functions, which at some point may make things marginally more difficult to debug. You might want to consider standardizing on either

CONCATENATE("😬",Roommate," ",DATETIME_FORMAT(Date,'MMMM')," ",{Bill Name})

or

"😬"&Roommate&" "&DATETIME_FORMAT(Date,'MMMM')&" "&{Bill Name}

I’ve always preferred the latter, just so I don’t have to try to type CONCATENATE() so many times. :winking_face:

[Matthew_Sulliva]

Was slammed at work and finally got around to this exercise. Thank you so much! It all works now. Also needed to get my logic order right! What I’ll do for some sweet emojis. Thanks again. :slightly_smiling_face: