Return value based on years of service..UPDATED PART 2!

UPDATE: PART 2

thank you everyone for your help so far. Now it is time for part 2 of the sheet.

Engineers change seniority levels as they gain experience.

At 4 years of engineering they go from Junior to Intermediate
At 10 years they go to Senior

What I need now is a notification system. I have created two other columns called "Seniority Level Change - Intermediate" and "Seniority Level Change - Senior".

I want a corresponding year (maybe month, not sure yet) to pop up in this column stating when they should change their seniority level.

example.


Name.............1st year engineering.........current seniority level......Seniority Level Change - Intermediate.....Seniority Level Change - Senior

Bob Bob.................2012...............................junior.........................................2016........................................................2022


I've attached my current spreadsheet for your review. Any help is greatly appreciated!

Engineering Level.xls


thank you
Nick

Try
=LOOKUP(YEAR(TODAY())-2002,{0,4,10},{"Junior","Intermediate","Senior"})

Try:

=IF($Q$9-F3<=4,"Junior",IF($Q$9-F3<=10,"Intermediate","Senior"))

Not pretty, but try

=IF(ISBLANK(F3),"",IF((YEAR(NOW())-F3)<4,"Junior",IF(AND(YEAR(NOW())-F3>=4,YEAR(NOW())-F3<=10),"Intermediate",IF(YEAR(NOW())-F3>10,"Senior",""))))

Why are bumping this thread? Have you tried the suggestions at all? If there is a problem, then specifically state it!