Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Logic Formula for dates based on Date Range

Topic Labels: Formulas
Solved
Jump to Solution
256 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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!

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