How to make year update not on January first

I am working on a project where i have to create a function that automatically updates student year (i.e. freshman, sophomore, junior, senior) based on number of years from the current year the person will graduate. i have made a nested IF function that does just that, however there is a problem. the school year starts in august and ends in may, so all students get their status updated on January 1st while the school year is not over. Is there anyway to make the year update on say June 1st so the student year doesn't change prior to when it is supposed to?

Here is the function i have written so far:

=IF(Z6-YEAR(TODAY())=1,"senior",IF(Z6-YEAR(TODAY())=2,"junior",IF(Z6-YEAR(TODAY())=3,"sophomore",IF(Z6-YEAR(TODAY())=4,"freshman",IF(2020-YEAR(TODAY())<=0,"alumni","unlisted")))))

Z6 is the cell that generates graduation year for incoming freshman. the function is set up to continue to update until after they graduate

Try this ...

=IF(Z6+1-YEAR(TODAY()+214) =1, ....

unfortunately that cause the graduation year to be increased by 1, so instead of incoming freshman having a graduation year of 2021 it will be a graduation year of 2022 which will cause them to be updated half a year late as opposed to early

Originally Posted by jackdu4

unfortunately that cause the graduation year to be increased by 1

Change: =IF(Z6+1-YEAR(TODAY()+214) =1, ....

To: =IF(Z6-YEAR(TODAY()+214) =1, ....

Originally Posted by Phuocam

Change: =IF(Z6+1-YEAR(TODAY()+214) =1, ....

To: =IF(Z6-YEAR(TODAY()+214) =1, ....

sorry, misread your response the first time, that might actually work!!

You're welcome, good luck!

last thing, do you know of the best way to test how that function will act over time so i can make sure it is correct???

=IF(Z6+1-YEAR(A1+214) =1, ....

Change the value in A1.

thanks so much! you've been a huge help!