# How to make year update not on January first

1. ## 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

2. ## Re: How to make year update not on January first

Try this ...

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

3. ## Re: How to make year update not on January first

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

4. ## Re: How to make year update not on January first

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

5. ## Re: How to make year update not on January first

Originally Posted by Phuocam
Change: =IF(Z6+1-YEAR(TODAY()+214) =1, ....

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

6. ## Re: How to make year update not on January first

You're welcome, good luck!

7. ## Re: How to make year update not on January first

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???

8. ## Re: How to make year update not on January first

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

Change the value in A1.

9. ## Re: How to make year update not on January first

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

There are currently 1 users browsing this thread. (0 members and 1 guests)