I have a list of data for days worked for thousands of employees. I need to find and highlight each instance where, starting on a Sunday, the employee worked seven consecutive days in a row (Sun to Sat). I have the date columns A and B, the number "0" or "1" in the Column C, and the day of the week in the Column D, as shown below. To complicate matters, I only need to look at those situations where there is a "1" in the Column C next to the "SUN" - if there is a "0" I can ignore it.
20131124 11/24/13 1 SUN
20131125 11/25/13 0 MON
20131126 11/26/13 0 TUE
20131127 11/27/13 0 WED
20131128 11/28/13 0 THU
20131129 11/29/13 0 FRI
20131130 11/30/13 1 SAT
The above example meets the criteria. There is a "1" next to "SUN" and the employee worked SUN - SAT consecutively. At this point, I want to highlight the values in Column D (SUN-SAT) with a fill color (it doesn't matter which color). Attached is a sample file that shows more clearly what it would look like.
Any help would be appreciated.
Bookmarks