# Re: Multi-year "Season" off date range

Solved
342 0
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

In the theatre world our "fiscal years" are actually seasons that start in the fall and end in the spring.  And specifically, there are two big "quarters" = a fall/spring season, and a summer season. More complex than just "quarter 1" plus "year." and I don't think I can use the MONTH function.

I want to be able to use an if then statement that I can set once and forget it without having to create a new formula field when each new season rolls around.

If the date range is between 09/01/(yyyy) - 05/31/(yyyy+1), it's displayed as the "(yyyy)/(yyyy+1) Season"

Ex:  Engagement end date is 09/02/2022.  It would be displayed in the column as "2022/2023 season"

If the date range is between 06/01/yyyy - 08/31/yyyy, it's "yyyy Season"

Ex: Engagement end date is 0701/2023. It would be displayed in the column as "Summer 2023 season"

Is this possible??

M

1 Solution

Accepted Solutions
16 - Uranus

Probably?  I threw something together here to get you started:

``````IF(
AND(
MONTH(Date) > 8,
MONTH(Date) <= 12
),
YEAR(Date) & "/" & (YEAR(Date) + 1) & " Season",
"Summer " & YEAR(Date) & " Season"
)``````

Don't think I fully understand though so I probably got something wrong

3 Replies 3
16 - Uranus

Probably?  I threw something together here to get you started:

``````IF(
AND(
MONTH(Date) > 8,
MONTH(Date) <= 12
),
YEAR(Date) & "/" & (YEAR(Date) + 1) & " Season",
"Summer " & YEAR(Date) & " Season"
)``````

Don't think I fully understand though so I probably got something wrong

5 - Automation Enthusiast

Thank you so much!!! Wow!  I knew there was some way to use the IF AND functions for this, but didn't know how to execute it.  You just made my day:)

5 - Automation Enthusiast

For anyone curious, here is my finished product -

I also had some records that don't have an engagement end date so I have an additional "Archival Year" column (for things like resume lines.)

I don't know how to do the tabbing thing but I'm sure you'll understand.  lol

IF(AND({Engagement End Date}=BLANK(), {Archival Year}=BLANK()), "",

(IF({Engagement End Date}=BLANK(),{Archival Year} & " (Archival)",

(IF(MONTH({Engagement End Date}) > 8, YEAR({Engagement End Date}) & "/" & (YEAR({Engagement End Date}) + 1) & " Season",

(IF(MONTH({Engagement End Date}) <=5, (YEAR({Engagement End Date}) - 1) & "/" & YEAR({Engagement End Date}) & " Season",

"Summer " & YEAR({Engagement End Date}) & " Season")))))))