# Counting formula that will treat consecutive dates as one occurrence

#### bchez

##### Board Regular
Need a formula in Column G that will start with 1 as soon as column D >40 and add one occurrence for each time a person calls in sick....The tricky part is I need consecutive dates to be treated as one occurrence. Here are two tabs for the example. The first is the Summary tab and the second is where the data gets input.

ABCDEFGH
 Date
<colgroup><col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;"> <tbody> </tbody>
 1/1/2018
<colgroup><col width="181" style="width: 136pt; mso-width-source: userset; mso-width-alt: 6619;"> <tbody> </tbody>
 Alvarez, D
<colgroup><col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;"> <tbody> </tbody>
 Sick Child
<colgroup><col width="181" style="width: 136pt; mso-width-source: userset; mso-width-alt: 6619;"> <tbody> </tbody>
 Employee Illness
<colgroup><col width="141" style="width: 106pt; mso-width-source: userset; mso-width-alt: 5156;"> <tbody> </tbody>
 OR Safe Sick
<colgroup><col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;"> <tbody> </tbody>
 Tardy
<colgroup><col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <tbody> </tbody>
 LOA
<colgroup><col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;"> <tbody> </tbody>
 Unexcused Employee Illness
<colgroup><col width="207" style="width: 155pt; mso-width-source: userset; mso-width-alt: 7570;"> <tbody> </tbody>
 Total Unexcused
<colgroup><col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <tbody> </tbody>
 Alvarez, D
<colgroup><col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;"> <tbody> </tbody>
 0
<colgroup><col width="181" style="width: 136pt; mso-width-source: userset; mso-width-alt: 6619;"> <tbody> </tbody>
 9
<colgroup><col width="141" style="width: 106pt; mso-width-source: userset; mso-width-alt: 5156;"> <tbody> </tbody>
 52
<colgroup><col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;"> <tbody> </tbody>
 0
<colgroup><col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <tbody> </tbody>
 0
<colgroup><col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;"> <tbody> </tbody>
 1
<colgroup><col width="207" style="width: 155pt; mso-width-source: userset; mso-width-alt: 7570;"> <tbody> </tbody>
 1
<colgroup><col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <tbody> </tbody>

<tbody>
</tbody>

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### bchez

##### Board Regular
Here is where the data gets input Assume Jan 1 is the date that causes Col D in the other spreadsheet to be >40. I need to start counting illness occurrences at 1, and count 1 for every time they are ill. The tricky part again is that consecutive days are treated as only 1 occurrence. So Jan 1 is one occurrence (the first one). Jan 3 and 4 count as one occurrence and Jan 6 would be another occurrence. Is there any way to do this? I have not been able to account for the consecutive days. I don't do VBA so need it to be a straight Excel formula. Thanks in advance.

 A B C D DATE DAY ALVAREZ HRS 1/1/17 SUN Employee Illness 8 1/2/17 MON 1/3/17 TUE Employee Illness 8 1/4/17 WED Employee Illness 8 1/5/17 THU 1/6/17 FRI Employee Illness 8

<tbody>
</tbody>

Replies
3
Views
692
Replies
1
Views
2K
Replies
3
Views
3K
Replies
1
Views
644
Replies
1
Views
526

Threads
1,191,670
Messages
5,987,951
Members
440,121
Latest member
eravella

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back