So I need a function with possibly quite a few nested ifs and some exceptions. I am needing to know when the next time one of my employees needs an evaluation. Evaluations occur annually on an employee's date of hire except for the first one, which occurs 6 months after their date of hire. There are other exceptions as well. If an employee has a position change like a promotion, they have an evaluation annually based off of the promotion date, again except for the first one which is 6 months after the promotion date.

I am wondering if I can have one cell under evaluation due date that could follow all of this criteria.

I have Employee (A1) -
Hire Date (B1)
Last status change (C1)(If it's possible to have this blank and have the formula revert to hire date if there it is blank, that would be helpful)
Last evaluation date (if they haven't had one, it's n/a and should be 6 months after hire date/status change)

The progression is an employee is hired, they have a 6 month evaluation then an annual evaluation every year after that on their hire date
If an employee is promoted, they have a 6 month evaluation then an annual evaluation every year after that on their promotion date.

I hope this makes sense and can be solved.

Thanks!