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!

-Heather

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