Identify consecutive leave types

[Sandeep Warrier]

Hi All,

See the table below. I need to identify if any employees have taken any leaves before or after a paternity leave, to make it similar to an extended paternity leave. In the first example, employee took an annual leave on 13th March, followed by paternity leave starting 14th March. There can be a max 3 days difference between end of one leave type and beginning of another leave type (see 2nd example). In this example, I have used only Annual Leave, but there can be any other leave type (sick, unpaid, etc.) but it must be clubbed with a paternity leave. Any ideas on how to approach this?

Book6
	A	B	C	D	E
1	Emp ID	Start Date	End Date	Leave Type	Extended Paternity?
2	1111	19/12/2022	19/12/2022	Annual Leave	No
3	1111	13/01/2023	13/01/2023	Annual Leave	No
4	1111	16/01/2023	16/01/2023	Annual Leave	No
5	1111	13/03/2023	13/03/2023	Annual Leave	Yes
6	1111	14/03/2023	27/03/2023	Paternity Leave	Yes
7	1112	22/07/2022	22/07/2022	Annual Leave	No
8	1112	23/07/2022	22/07/2022	Annual Leave	No
9	1112	29/09/2022	07/10/2022	Annual Leave	No
10	1112	06/02/2023	19/02/2023	Paternity Leave	No
11	1113	23/08/2022	05/09/2022	Paternity Leave	Yes
12	1113	07/09/2022	13/09/2022	Annual Leave	Yes
13	1114	14/07/2022	27/07/2022	Paternity Leave	No
14	1115	23/05/2022	29/05/2022	Paternity Leave	Yes
15	1115	30/05/2022	01/06/2022	Annual Leave	Yes
16	1116	02/06/2022	03/06/2022	Annual Leave	No
17	1116	17/06/2022	17/06/2022	Annual Leave	No
18	1116	24/06/2022	24/06/2022	Annual Leave	No
19	1116	08/07/2022	08/07/2022	Annual Leave	Yes
20	1116	11/07/2022	24/07/2022	Paternity Leave	Yes
Sheet1

 

[DanteAmor]

Hi @Sandeep Warrier. Thanks for posting on MrExcel.

Check if the following helps you:

Dante Amor
	A	B	C	D	E
1	Emp ID	Start Date	End Date	Leave Type	Extended Paternity?
2	1111	19/12/2022	19/12/2022	Annual Leave	No
3	1111	13/01/2023	13/01/2023	Annual Leave	No
4	1111	16/01/2023	16/01/2023	Annual Leave	No
5	1111	13/03/2023	13/03/2023	Annual Leave	Yes
6	1111	14/03/2023	27/03/2023	Paternity Leave	Yes
7	1112	22/07/2022	22/07/2022	Annual Leave	No
8	1112	23/07/2022	22/07/2022	Annual Leave	No
9	1112	29/09/2022	07/10/2022	Annual Leave	No
10	1112	06/02/2023	19/02/2023	Paternity Leave	No
11	1113	23/08/2022	05/09/2022	Paternity Leave	Yes
12	1113	07/09/2022	13/09/2022	Annual Leave	Yes
13	1114	14/07/2022	27/07/2022	Paternity Leave	No
14	1115	23/05/2022	29/05/2022	Paternity Leave	Yes
15	1115	30/05/2022	01/06/2022	Annual Leave	Yes
16	1116	02/06/2022	03/06/2022	Annual Leave	No
17	1116	17/06/2022	17/06/2022	Annual Leave	No
18	1116	24/06/2022	24/06/2022	Annual Leave	No
19	1116	08/07/2022	08/07/2022	Annual Leave	Yes
20	1116	11/07/2022	24/07/2022	Paternity Leave	Yes
Hoja1

Cell Formulas
Range	Formula
E2:E20	E2	=IF(OR(AND(A2=A3,OR(D2="Paternity Leave",D3="Paternity Leave"),B3-C2<=3), AND(A1=A2,OR(D1="Paternity Leave",D2="Paternity Leave"),B2-N(C1)<=3)),"Yes","No")

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------