#1 Today, 05:03 PM
upstate_steve is Online:
Registered User Join Date: Apr 2004
Posts: 22

finding consecutive values in array

--------------------------------------------------------------------------------

Hello

I have a database of records that contains a date field. I need to write an array formula that counts values corresponding to those dates only in cases where five or more consecutive dates appear.

The records are for days taken off by employees--one record for each day taken.

So, lets say an employee is sick four days in a row.

Code---Date----Hours

S-------01/03----7.5
S-------01/04----7.5
S-------01/05----7.5
S-------01/06----7.5

Hours are not counted.

Now let's say an employee is out Thursday through the following Wednesday

Code---Date----Hours

S-------01/13----7.5
S-------01/14----7.5
S-------01/17----7.5
S-------01/18----7.5
S-------01/19----7.5

Hours are counted. (Note that, b/c of weekends, the date values may not be numerically consecutive.)

All consecutive dates are counted where there are five or more, so the number of records in a consecutive series will vary. Also, an employee may occasionally have multiple records with the same date.

I'm not sure where to begin with this. My array formulas have never needed to evaluate records against previous or subsequent rows.
Thanks

Steve