Identify consecutive leave types

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,679
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
ABCDE
1Emp IDStart DateEnd DateLeave TypeExtended Paternity?
2111119/12/202219/12/2022Annual LeaveNo
3111113/01/202313/01/2023Annual LeaveNo
4111116/01/202316/01/2023Annual LeaveNo
5111113/03/202313/03/2023Annual LeaveYes
6111114/03/202327/03/2023Paternity LeaveYes
7111222/07/202222/07/2022Annual LeaveNo
8111223/07/202222/07/2022Annual LeaveNo
9111229/09/202207/10/2022Annual LeaveNo
10111206/02/202319/02/2023Paternity LeaveNo
11111323/08/202205/09/2022Paternity LeaveYes
12111307/09/202213/09/2022Annual LeaveYes
13111414/07/202227/07/2022Paternity LeaveNo
14111523/05/202229/05/2022Paternity LeaveYes
15111530/05/202201/06/2022Annual LeaveYes
16111602/06/202203/06/2022Annual LeaveNo
17111617/06/202217/06/2022Annual LeaveNo
18111624/06/202224/06/2022Annual LeaveNo
19111608/07/202208/07/2022Annual LeaveYes
20111611/07/202224/07/2022Paternity LeaveYes
Sheet1
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi @Sandeep Warrier. Thanks for posting on MrExcel.

Check if the following helps you:

Dante Amor
ABCDE
1Emp IDStart DateEnd DateLeave TypeExtended Paternity?
2111119/12/202219/12/2022Annual LeaveNo
3111113/01/202313/01/2023Annual LeaveNo
4111116/01/202316/01/2023Annual LeaveNo
5111113/03/202313/03/2023Annual LeaveYes
6111114/03/202327/03/2023Paternity LeaveYes
7111222/07/202222/07/2022Annual LeaveNo
8111223/07/202222/07/2022Annual LeaveNo
9111229/09/202207/10/2022Annual LeaveNo
10111206/02/202319/02/2023Paternity LeaveNo
11111323/08/202205/09/2022Paternity LeaveYes
12111307/09/202213/09/2022Annual LeaveYes
13111414/07/202227/07/2022Paternity LeaveNo
14111523/05/202229/05/2022Paternity LeaveYes
15111530/05/202201/06/2022Annual LeaveYes
16111602/06/202203/06/2022Annual LeaveNo
17111617/06/202217/06/2022Annual LeaveNo
18111624/06/202224/06/2022Annual LeaveNo
19111608/07/202208/07/2022Annual LeaveYes
20111611/07/202224/07/2022Paternity LeaveYes
Hoja1
Cell Formulas
RangeFormula
E2:E20E2=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
--------------
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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

Which adblocker are you using?

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
Back
Top