Help

Multi-year "Season" off date range

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
2762 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Maria_Miller
5 - Automation Enthusiast
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
TheTimeSavingCo
18 - Pluto
18 - Pluto

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

Screenshot 2023-03-18 at 3.30.18 PM.png

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

See Solution in Thread

3 Replies 3
TheTimeSavingCo
18 - Pluto
18 - Pluto

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

Screenshot 2023-03-18 at 3.30.18 PM.png

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

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