I am attempting to make an excel sheet to track the number of times an individual is either late or gone for a period of time, consecutive days only counting as one infraction, in a rolling 12 week period. My thought is each time there is a break in the dates there is another infraction (Example: 1 jan, 2 jan, 5 jan), thus comparing adjacent cells to see if the dates are consecutive would be a way to count the number of time the individual was gone for any period of time. The end result I am aiming for is to have the individuals names down column A and just enter the date into the next column for every day that they are late or gone and have the number of infractions shown in a separate cell. This separate cell is then used with conditional formatting to change the individuals name cell to green for 1, yellow for 2, and red for 3 and greater.
I attempted using just conditional formatting that works for changing each non consecutive dates color to just one color but have not found a way to count those times. I have also tried an equation similar to the conditional formatting (=COUNTIF(7:7,A7+1