Multi-year "Season" off date range

[Maria_Miller]

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

[TheTimeSavingCo · Accepted solution]

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

[Maria_Miller]

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

[Maria_Miller]

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