Feb 06, 2022 05:31 PM
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”)))))
Solved! Go to Solution.
Feb 07, 2022 09:48 AM
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.
Feb 07, 2022 07:05 AM
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”,
“”
)
)
)
)
)
Feb 07, 2022 09:48 AM
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.
Feb 07, 2022 10:11 AM
Hi @Jeremy_Oglesby,
That is a much better approach and thank you for adding that to this thread!
Chris
Feb 07, 2022 04:57 PM
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')
Jul 03, 2023 11:03 AM
@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.