Skip to main content
Solved

Calculating school year using date field

  • February 7, 2022
  • 5 replies
  • 90 views
K

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

Best answer by Jeremy_Oglesby

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

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.

5 replies

W
Forum|alt.badge.img+12

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”,
“”
)
)
)
)
)

J
Forum|alt.badge.img+18
  • Jeremy_Oglesby
  • Inspiring
  • Answer
  • February 7, 2022

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

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.

JonathanBowen
K
Kamille_Parks11
W
Forum|alt.badge.img+12

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

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.


Hi @Jeremy_Oglesby,

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

Chris

Alexey_Gusev
Forum|alt.badge.img+25

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?” :slightly_smiling_face:
but, it works correct for June, 30 :slightly_smiling_face: 

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')
J
L
Forum|alt.badge.img+3
  • LB_MHIR
  • New Participant
  • July 3, 2023

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

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.


@Jeremy_Oglesby How would you write this differently if the academic/school year went the whole year, from August 15 of one year to August 14 of the following (ex 8/15/22-8/14/23)? I've been trying to get it to work by changing the formula in the second part to reflect If the MONTH is = 8 AND the DAY is less than or equal to 14, but I'm getting an error. 

 
IF(
  OR(
    MONTH({Position start date}) > 8, 
    AND(
      MONTH({Position start date}) = 8, 
      DAY({Position start date}) > 15
    )
  ), 
  YEAR({Position start date}) & '-' & RIGHT((YEAR({Position start date}) + 1)&'', 2) & ' Academic Year', 
  IF(
    AND(
      MONTH({Position start date}) = 8, 
      DAY({position start date}) <= 14
    '', 
    YEAR({Position start date}) - 1 & '-' & RIGHT(YEAR({Position start date})&'', 2) & ' Academic Year'
  )
)
Terms & ConditionsAccessibility statement