Calculating school year using date field

Hi! I am looking for a formula to calculate the school year. I have a date field that I want to use. The formula I thought would work is not working. Please see below:

IF(AND(Date>“08/15/2021”, Date<“06/30/2022”), “2021-22 School Year”, IF(AND(Date>“8/15/2022”, Date<“6/30/2023”), “2022-23 School Year”, IF(AND(Date>“08/15/2023”, Date<“06/30/2024”), “2023-24 School Year”, IF(AND(Date>“08/15/2024”, Date<“06/30/2025”), “2024-25 School Year”, IF(AND(Date>“08/15/2025”, Date<“06/30/2026”), “2025-26 School Year”)))))

Hi @Kim_Ferar,

Welcome to the community!

If I’m not mistaken, the “IF” statement needs a not true “then” or second value. I laid out the formula in Notepad so it was easier to see as well (see screenshot below). You could simply assign a blank value with “” or you could say something like “Add more school years”.

Let me know if you have any questions.

Chris

PS: Here is the formula that returns a blank:

IF(AND(Date>“08/15/2021”, Date<“06/30/2022”),
“2021-22 School Year”,
IF(AND(Date>“8/15/2022”, Date<“6/30/2023”),
“2022-23 School Year”,
IF(AND(Date>“08/15/2023”, Date<“06/30/2024”),
“2023-24 School Year”,
IF(AND(Date>“08/15/2024”, Date<“06/30/2025”),
“2024-25 School Year”,
IF(AND(Date>“08/15/2025”, Date<“06/30/2026”),
“2025-26 School Year”,
“”
)
)
)
)
)

Hi @Kim_Ferar (and @Williams_Innovations),

You are using hard-coded dates in your formula, which means you will have to continually update this formula in the future for it to work properly (wishing you many happy years of teaching and organizing your materials in Airtable :slight_smile:).

Here’s a variation of your formula that distills the mathematical principle out of the hard-coded dates, so that the formula will work with any date, not just through 2026.

IF(
  OR(
    MONTH(Date) > 8, 
    AND(
      MONTH(Date) = 8, 
      DAY(Date) > 15
    )
  ), 
  YEAR(Date) & '-' & RIGHT((YEAR(Date) + 1)&'', 2) & ' School Year', 
  IF(
    MONTH(Date) > 6, 
    '', 
    YEAR(Date) - 1 & '-' & RIGHT(YEAR(Date)&'', 2) & ' School Year'
  )
)

The logic parts of this should be straightforward, I think – the only tricky thing I did in this formula, that I’d like to point out, is the use of &'' at the end of each YEAR() function inside the RIGHT() functions. That use of &'' is just there to coerce the number that the YEAR() function returns into behaving like a string instead, so that the RIGHT() function can trim off all but the last two characters of the string.

3 Likes

Hi @Jeremy_Oglesby,

That is a much better approach and thank you for adding that to this thread!

Chris

That’s rather for fun and demo of alternative approach.
it’s not about ‘readable’, 2-3 months later i would be surprised “what’s this?” :slight_smile:
but, it works correct for June, 30 :slight_smile:

IF(
((DAY(Date)>15)+MONTH(Date)>8)+((DAY(Date)=30)+(MONTH(Date)<7)),
''&(YEAR(Date)-MONTH(Date)<7)&'-'&(YEAR(Date)-2000+(MONTH(Date)>7))
&' School years')
1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.