# If Except Or And

1. ## If Except Or And

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!

2. ## Re: If Except Or And

Please attach a workbook with typical layout and some sample data.
Also, write in expected result or describe the logic and fill in results manually.

3. ## Re: If Except Or And

For confidentiality reasons, I can't upload my original document, but here is a blank one I created that has everything that I would need.

4. ## Re: If Except Or And

A1:E7 contains the inputs and the computed Evaluation Due dates for the test cases supplied by Jeremy. Give or take a day the calculated values match the expected results.

Cell K1 is named "today" and for testing purposes currently contains a fixed date. When using this workbook for real the contents of K1 should be replaced with: =today()

The formula to compute due date is in E2 and copied down and is:
Formula:

For clarity and further test/debug I have left in place in G1:K11 the step by step pieces used to create this formula. These columns can be deleted if you wish.

5. ## Re: If Except Or And

It's not coming back correctly for me. When I put this in mine, it comes back #NAME? I am using different tables and such, so C3 would be listed as tblEmployeeFiles[@[Evaluation]]. I also worked a little on the one you did and sometimes, the answers were coming back a year and 6 months a head of time. Example, I put a hire date in as 1/1/2018, the result came back as 6/1/2019. I tried putting in earlier dates for the hire date as well without putting anything in the last eval date and it still comes back as 2, 3, 4...years later.

6. ## Re: If Except Or And

With regard to the #NAME error, the formula I provided relies on a cell somewhere being named "today" and containing today's date (or another date for ease of testing). Did you do that in your real workbook? Another option, in the formula would be to replace the occurrences of "today" with "today()" and forget about the named cell.

While you respond to the above, I'll take a look at the failure cases that you have provided.

7. ## Re: If Except Or And

I put a hire date in as 1/1/2018, the result came back as 6/1/2019. I tried putting in earlier dates for the hire date as well without putting anything in the last eval date and it still comes back as 2, 3, 4...years later.
I have assumed (wrongly maybe) that the process of 6 mo and 12 mo reviews has been complied with. An employee with a hire date of 1/1/2018 and no "last eval date" has missed 2 reviews: his 6 mo review around 6/1/2018 and his annual review around 1/1/2019. With earlier hire dates and no "last eval date" even more reviews will have been missed. What should "Eval due date" show in these cases? The date of the initial 6 monthly review? Let me know.

Thanks

8. ## Re: If Except Or And

Yeah. We have been severely lacking in our Evaluations to the point where an employee has gone 2 or even 3 years without completing one. I would keep the evaluation due date as the 6 months after. This would give me some room to do conditional formatting in the case if it's before today and how far before today it is.

9. ## Re: If Except Or And

Try this in G2 filled down. Did this on original upload without Table syntax.
Formula:

10. ## Re: If Except Or And

Hmm. The problem with that one is that it uses the date of the last evaluation (D2) as the basis for the next evaluation due date rather than based on their hire date (B2) or their last status change (C2). Does that make sense?

If a person is hired or changed positions they have a 6 month evaluation based on their hire/new position date then an annual review every year after that on those annual dates.

11. ## Re: If Except Or And

CHOOSE(MATCH(MAX(B2:D2),B2:D2,0),6,6,12) and the EOMONTH( ...,+DAY(MAX(B2:C2)) parts should take care of that.

Can you upload another example that illustrates exceptions?

12. ## Re: If Except Or And

Here's the document that was submitted to me. I added a row that included someone who was hired less than a year ago, but already had their 6 month evaluation. The due date should go to 6 months later where the annual is a year after their hire date, but it is a year past the date of the last evaluation.

13. ## Re: If Except Or And

OK.

This changes everything. That exception contradicts the criteria RE: the last evaluation. I am confused.

I will have to rethink this one.

14. ## Re: If Except Or And

Try this change in column G.
Formula:
It returns as indicated in the latest upload.

15. ## Re: If Except Or And

I guess I'm confused too.
You say in post #1: "Evaluations occur annually on an employee's date of hire except for the first one, which occurs 6 months after their date of hire" Why then in Grace's case is the Eval Due date not 1-Jan-19?

This makes sense as the 2019 review was completed a few days early.
Employee Joe: Hire date: 1/18/2016, Last eval: 1/1/2019 => Next Eval: 1/18/2020

Employee Fred: Hire date: 1/18/2016, Last eval: 2/1/2018 => Next Eval: ???
I imagine, as the 2/1/2018 review was completed a month late, that the Next Eval date should be 1/19/2019 right?
If so then where is the break point between these two cases? That is, when does a late review from last year become an early review for this year?

16. ## Re: If Except Or And

Hey guys, This may have just turned really complicated and I might just input all of the due dates by hand. The issue I guess is just human error. My managers are so backed up with evaluations that it's just a mess and they turn a 6 month evaluation into an annual one because it's 5 months late...Maybe when we get back on track and do evaluations when they are supposed to happen, I will revisit this, but it's just becoming a really complicated thing. Thanks for helping out. I really appreciate it.

17. ## Re: If Except Or And

• Use concise, accurate thread titles.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

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