Skip to main content
Solved

Multi-year "Season" off date range

  • March 16, 2023
  • 3 replies
  • 36 views
M
Forum|alt.badge.img+2

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

Best answer by TheTimeSavingCo

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

TheTimeSavingCo
Forum|alt.badge.img+31
  • TheTimeSavingCo
  • Brainy
  • 6414 replies
  • Answer
  • March 18, 2023

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

Adam, Airtable Consultant - thetimesaving.company
M
Forum|alt.badge.img+2
  • Maria_Miller
  • Author
  • New Participant
  • 2 replies
  • March 19, 2023

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

M
Forum|alt.badge.img+2
  • Maria_Miller
  • Author
  • New Participant
  • 2 replies
  • March 20, 2023

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


Terms & ConditionsAccessibility statement