Counting Sick day occurrences based on name

[AndrewD04]

Good Morning,

Sorry i cant upload a copy as my excel wont allow me to.

I have a column of names in column C then a list of dates in column M - what i am trying to do is get a formula to count the list of sick days however if they are consecutive such as 16/05,17/05 and 18/05 count this as 1 occurrence.

This is an example of what i am trying to achieve, i have tried multiple ways including some other threads on here but none seem to work like i need any help is much appreciated

	Sick Day	Occurrence
Agent 1	17-May​	1​
Agent 1	18-May​	0​
Agent 1	23-May​	1​
Agent 2	12-May​	1​
Agent 2	23-May​	1​
Agent 3	10-May​	1​
Agent 3	11-May​	0​

 

[Kerryx]

Maybe like this?

Book1
	A	B	C	D
1	Name	Date	Occ	Test
2	Agent 1	17/5/23	1	1
3	Agent 1	18/5/23	0	0
4	Agent 1	23/5/23	1	1
5	Agent 2	12/5/23	1	1
6	Agent 2	23/5/23	1	1
7	Agent 3	10/5/23	1	1
8	Agent 3	11/5/23	0	0
9	Agent4	20/06/2023		1
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=IF(A2=A1,IFERROR(IF(DAYS(B2,B1)>1,1,0),1),1)