Logic Formula for dates based on Date Range

[Ted_Rogers]

Hello! My use-case may be unique, as I am project managing a movie theater.
I have a Calendar view of all confirmed films by Date Range defined by a “Start Date” and “End Date.” Some films are on runs (typically Friday-Thursday ie Start Date 10/29/21, End Date 11/4/21), and others are single events (ie Start Date 10/29/21, End Date 10/29/21).

I have written a formula to categorize films by status (“Coming Soon”, “On Screen”, “Closed”) as follows:

IF(
IS_AFTER(
{Start Date},
TODAY()
),
"Coming Soon",
IF(
IS_AFTER(
{End Date},
TODAY()
),
"On Screen",
"Closed"
)
)

My issue is that single events will appear as “Closed” on the day they open (duh). This status is easily fixed by setting the End Date to the day after it opens, however this yields two-day-long entries for single events on the graphical calendar, which is obviously misleading for our staff.

Any recommendations for rethinking my formula (an IS_SAME function?), date-range definitions for the calendar, or an entirely different approach?
Thanks!

[ScottWorld · Accepted solution]

Welcome to the community, @Ted_Rogers!

It seems like your formula will also have the same problem with multi-day films, because it will show multi-day films as “closed” on the final day of the film.

So yes, you’d want to add some additional criteria into your formula.

You can compare if two date fields are the same by just using the equal sign (i.e. {Start Date} = {End Date}).

So you’d probably want to add something like this into your formula:

IF(
Today() = {End Date},
"On Screen"
)

[Ted_Rogers]

Incredible! So simple! Thank you!

[Ted_Rogers]

Order of operations is important, so here’s the full finished formula:

IF(
IS_AFTER(
{Start Date},
TODAY()
),
"Coming Soon",
IF(
TODAY() = {End Date},
"On Screen",
IF(
IS_AFTER(
{End Date},
TODAY()
),
"On Screen",
"Closed"
)))