+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Consecutive Numbers Over 110

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2007

    Consecutive Numbers Over 110

    So I keep a spreadsheet to track eligibility for insurance benefits.

    In order to be eligible, an employee has to have worked 12 consecutive months at 110 or more hours per month. If they work less than 110 hours any month, the count starts over.

    I need a formula that will tell me when they are eligible for benefits.

    On my spreadsheet:

    Employee 1 worked 110 hours or more for 12 consecutive months (06/09 - 05/10) He would be eligible on 05/10. In cell H2, I would like it to display the date Employee 1 hit the eligibility (The Heading date), 05/10.

    Employee 2 didn't start working until 03/10. He worked 110 hours or more for 12 consecutive months (03/10 - 02/11). I want cell H3 to display 02/11.

    Employee 3 worked 06/09 - 10/09 at 110 hours or more, BUT he only worked 89.5 hours in 11/09, which means his count would start over, he then worked 12/09 - 11/10 at 110 hours or more which would make him eligible 11/10.

    Employee 4 started working 07/10 and has only worked 11 consecutive months at 110 hours or more, cell H5 should remain blank until he works one more month at 110 hours or more.

    Employee 5 has worked every month, some months at 110 hours or more, some months less than 110. He has not worked 12 consecutive months at 110 hours or more so cell H6 should remain blank.

    I add columns to the end each month with their hours.

    I tried my best explaining, please let me know if you have any questions.

    I would be SOOOO grateful if someone could assist me with a formula, it's really hard tracking eligibility for so many employees manually!

    Thank you!

    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    Irvine, CA
    MS-Off Ver
    Excel 2010

    Re: Consecutive Numbers Over 110

    I added a couple OFFSET formulas to get what I think you want...
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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