Help

Logic Formula for dates based on Date Range

Topic Labels: Formulas
Solved
Jump to Solution
972 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Ted_Rogers
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

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

See Solution in Thread

3 Replies 3
ScottWorld
18 - Pluto
18 - Pluto

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

Incredible! So simple! Thank you!

Ted_Rogers
5 - Automation Enthusiast
5 - Automation Enthusiast

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