Help

Re: Calculating school year using date field

Solved
Jump to Solution
1132 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Kim_Ferar
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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.

See Solution in Thread

5 Replies 5
Williams_Innova
7 - App Architect
7 - App Architect

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

image

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

Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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

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

@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'
  )
)