+ Reply to Thread
Results 1 to 17 of 17

If Except Or And

  1. #1
    Registered User
    Join Date
    12-13-2018
    Location
    Ferndale, WA
    MS-Off Ver
    Microsoft Office Standard 2016
    Posts
    15

    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. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    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.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Registered User
    Join Date
    12-13-2018
    Location
    Ferndale, WA
    MS-Off Ver
    Microsoft Office Standard 2016
    Posts
    15

    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.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    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: copy to clipboard
    Please Login or Register  to view this content.


    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-13-2018
    Location
    Ferndale, WA
    MS-Off Ver
    Microsoft Office Standard 2016
    Posts
    15

    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. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    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. #7
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    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. #8
    Registered User
    Join Date
    12-13-2018
    Location
    Ferndale, WA
    MS-Off Ver
    Microsoft Office Standard 2016
    Posts
    15

    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. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: If Except Or And

    Try this in G2 filled down. Did this on original upload without Table syntax.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave

  10. #10
    Registered User
    Join Date
    12-13-2018
    Location
    Ferndale, WA
    MS-Off Ver
    Microsoft Office Standard 2016
    Posts
    15

    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. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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. #12
    Registered User
    Join Date
    12-13-2018
    Location
    Ferndale, WA
    MS-Off Ver
    Microsoft Office Standard 2016
    Posts
    15

    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.
    Attached Files Attached Files

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: If Except Or And

    Try this change in column G.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    It returns as indicated in the latest upload.
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    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?

    One additional question relative to your example for Joe Somebody:
    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

    But what about:

    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. #16
    Registered User
    Join Date
    12-13-2018
    Location
    Ferndale, WA
    MS-Off Ver
    Microsoft Office Standard 2016
    Posts
    15

    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. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: If Except Or And

    Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

    • Use concise, accurate thread titles.
    • Your post title should describe your problem, not your anticipated solution.
    • Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
    • 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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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