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