Calculating start based based on input of another start date and academic year

Hi there,

I'm not sure how to accomplish the following and would hugely appreciate any assistance or insight.

First question:
- Open the attached workbook and go to the second sheet titled Names.
- Please note, everyting highlighted in yellow I manually entered just to give an example of how I want everything to eventually work.
- The data points in columns A-F are inputs that will always be manually entered.
- I'm not sure what formula to use to populate the info in Column G (current grade). This needs to caculate based on the Starting Academic Year (Column E) and the Starting Grade with the Company (Column F) . For example, If you were in the 4th grade when you started with the company during the 2018-2019 Academic Year when, Column G would now show you're in the 5th grade, as it's now the 2019-2020 Academic Year.

Second question:
- In the workbook, go to the sheet titled Tracker
- Please note, everyting highlighted in yellow I manually entered just to give an example of how I want everything to eventually work.
- I'm not sure how to calculate the dates in columns E-T. I'm trying to find a way to calculate the past, current, and future start dates of each grade (along with one mid-year flagged date in May in Column P). once I work out how to calculate the current grade (shown in column E of this sheet, but calculated in the Names sheet).

- the data with the years, academic year start and end dates is in the sheet labeled Data.

Many thanks in advance!

Thank you very, very much!

Hi there!

Quick question regarding Q1 I asked. This is the formula you provided: Q1
G7 =INDEX(Data!$A$2:$A$17,MATCH(F7,Data!$A$2:$A$17,)+YEAR(TODAY())-LEFT(E7,4))

This worked perfectly until we hit 2020. Now the formula bumped everyone up one extra year in the current grade column. The formula works correctly again when I change the LEFT to RIGHT, but I'm not sure why. Do you know why this stopped working once we hit 2020? Many thanks in advance!