Counting formula that will treat consecutive dates as one occurrence

[bchez]

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.

A	B	C	D	E	F	G	H
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>

 

[bchez]

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>