Skip to main content

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

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

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


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

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


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

Reply


Terms & Conditions