+ Reply to Thread
Results 1 to 9 of 9

How to make year update not on January first

  1. #1
    Registered User
    Join Date
    05-02-2017
    Location
    atlanta, georgia
    MS-Off Ver
    2016
    Posts
    5

    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. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,571

    Re: How to make year update not on January first

    Try this ...

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

  3. #3
    Registered User
    Join Date
    05-02-2017
    Location
    atlanta, georgia
    MS-Off Ver
    2016
    Posts
    5

    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. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,571

    Re: How to make year update not on January first

    Quote Originally Posted by jackdu4 View Post
    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. #5
    Registered User
    Join Date
    05-02-2017
    Location
    atlanta, georgia
    MS-Off Ver
    2016
    Posts
    5

    Re: How to make year update not on January first

    Quote Originally Posted by Phuocam View Post
    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!!

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,571

    Re: How to make year update not on January first

    You're welcome, good luck!

  7. #7
    Registered User
    Join Date
    05-02-2017
    Location
    atlanta, georgia
    MS-Off Ver
    2016
    Posts
    5

    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. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,571

    Re: How to make year update not on January first

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

    Change the value in A1.

  9. #9
    Registered User
    Join Date
    05-02-2017
    Location
    atlanta, georgia
    MS-Off Ver
    2016
    Posts
    5

    Re: How to make year update not on January first

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] DateAdd from December to January not updating year
    By armlegx in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2016, 07:06 PM
  2. January of current year
    By sdjames1979 in forum Excel General
    Replies: 1
    Last Post: 09-03-2015, 07:01 AM
  3. [SOLVED] Change From 1 January 2015 Thursday to 1 January 2015 Thu
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-14-2015, 11:47 AM
  4. How change cell year dates to automatically update to new year
    By RonRich in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2015, 08:29 AM
  5. Automatically update year interval cycles from year to year
    By trumptight in forum Word Programming / VBA / Macros
    Replies: 8
    Last Post: 08-21-2014, 10:38 PM
  6. Update Total and the numbers that make up that number update also.
    By borjasanz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2013, 07:44 PM
  7. Replies: 3
    Last Post: 05-16-2012, 03:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1