Next Leave date

[sachin99]

Good Day Everyone,

I need a formula to get the next Leave date of an employee based on criteria.

1. Below is the table which shows date on which particular employee is on leave
2. Will enter date in other sheet in cell A2
3. we will also have employee name in this sheet
3. Cell next to employee name should give the next PL date for that employee depending on the date enter in the
Eg. 1. If Sachin is the employee and date enter in Celll A2 is 07-Jan-2018 then value in cell next to Sachin will be 10-Jan-2018

2. If Vikas is the employee and date enter in Celll A2 is 09-Jan-2018 then value in cell next to Sachin will be 12-Jan-2018

3. if the A2 cell is blank then today's date will be consider for calculation

Do let me know if you need any more information on my requirement.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Sachin[/TD]
[TD]Vikas[/TD]
[/TR]
[TR]
[TD]01-Jan-2018[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]02-Jan-2018[/TD]
[TD]PL[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]03-Jan-2018[/TD]
[TD]PL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-Jan-2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05-Jan-2018[/TD]
[TD][/TD]
[TD]PL[/TD]
[/TR]
[TR]
[TD]06-Jan-2018[/TD]
[TD][/TD]
[TD]PL[/TD]
[/TR]
[TR]
[TD]07-Jan-2018[/TD]
[TD][/TD]
[TD]PL[/TD]
[/TR]
[TR]
[TD]08-Jan-2018[/TD]
[TD][/TD]
[TD]PL[/TD]
[/TR]
[TR]
[TD]09-Jan-2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10-Jan-2018[/TD]
[TD]PL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-Jan-2018[/TD]
[TD]PL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-Jan-2018[/TD]
[TD]PL[/TD]
[TD]PL[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 196"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Regards
Sachin

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, welcome to the board!

Here is on option you can try to adapt to your actual set-up..:

Excel 2013/2016
	A	B	C
1	Date	Name	Next PL
2	09/01/2018	Sachin	10/01/2018
3	09/01/2018	Vikas	12/01/2018
Sheet2

Cell Formulas
Range	Formula
C2	=INDEX(Sheet1!$A$2:$A$100,MATCH(1,INDEX((Sheet1!$A$2:$A$100>=A2)*(INDEX(Sheet1!$B$2:$C$100,0,MATCH(B2,Sheet1!$B$1:$C$1,0))="PL"),0),0))

Excel 2013/2016
	A	B	C
1	Date	Sachin	Vikas
2	01/01/2018	-	-
3	02/01/2018	PL	-
4	03/01/2018	PL
5	04/01/2018
6	05/01/2018		PL
7	06/01/2018		PL
8	07/01/2018		PL
9	08/01/2018		PL
10	09/01/2018
11	10/01/2018	PL
12	11/01/2018	PL
13	12/01/2018	PL	PL
Sheet1

 

[Saneesh]

Hi Sachin,

Try this

[TABLE="width: 387"]
<tbody>[TR]
[TD="colspan: 3"]Sheet 2[/TD]
[/TR]
[TR]
[TD]A Column[/TD]
[TD]B Column[/TD]
[TD]C Column[/TD]
[/TR]
[TR]
[TD]Names[/TD]
[TD]From Date[/TD]
[TD]Next PL leave[/TD]
[/TR]
[TR]
[TD]Sachin[/TD]
[TD]04-Jan-18[/TD]
[TD="align: right"]01/12/2018[/TD]
[/TR]
[TR]
[TD]Vikas[/TD]
[TD]01-Jan-18[/TD]
[TD="align: right"]01/12/2018[/TD]
[/TR]
</tbody>[/TABLE]



Code:

Sub NextPL()


Dim a, b, c As Range
Dim i, lw, lww, j As Integer


lw = Sheets("sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row
lww = Sheets("sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row




For j = 2 To lww
Set a = ThisWorkbook.Sheets("sheet1").Range("A:A").Find(ThisWorkbook.Sheets("sheet2").Cells(j, 2).Value)


a = a.Address


a = Replace(a, "$", "")
a = Right(a, Len(a) - 1) + 1










Set b = ThisWorkbook.Sheets("sheet1").Range("A1:z1").Find(ThisWorkbook.Sheets("sheet2").Cells(j, 1).Value)


b = b.Column - 1


For i = a To lw


If Cells(i, 1).Offset(, b).Value = "PL" Then
ThisWorkbook.Sheets("sheet2").Cells(j, 3).Value = Cells(i, 1).Value
GoTo nextiteration
Else
End If
Next


nextiteration:
Next
End Sub
/>

 

[sachin99]

Hi ,

Thanks for quick and helpful reply...

One more addition to current thread...

can i get the number of leaves applied

Eg.

Date	Name	Next PL
Sachin
Vikas

<colgroup><col width="25px" style="background-color: rgb(218, 231, 245);"><col><col><col></colgroup><thead>
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]

</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]09/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]02/01/2018[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]09/01/2018[/TD]

[TD="align: right"]05/01/2018[/TD]

</tbody>

For Sachin it is 2 and for Vikas it is 4.

Regards
Sachin

 

[FormR]

Hi, like this maybe.

Excel 2013/2016
	A	B	C	D
1	Date	Name	Next PL	leaves applied
2	09/01/2018	Sachin	02/01/2018	2
3	09/01/2018	Vikas	05/01/2018	4
Sheet2

Cell Formulas
Range	Formula
D2	=COUNTIFS(INDEX(Sheet1!$B$2:$C$100,0,MATCH(B2,Sheet1!$B$1:$C$1,0)),"PL",Sheet1!$A$2:$A$100,"<"&A2)

 

[sachin99]

Hi, like this maybe.

Excel 2013/2016

	A	B	C	D
Date	Name	Next PL	leaves applied
Sachin
Vikas

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]09/01/2018[/TD]

[TD="align: right"]02/01/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]09/01/2018[/TD]

[TD="align: right"]05/01/2018[/TD]
[TD="align: right"]4[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=COUNTIFS(INDEX(Sheet1!$B$2:$C$100,0,MATCH(B2,Sheet1!$B$1:$C$1,0)),"PL",Sheet1!$A$2:$A$100,"<"&A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

Thanks for the formula...

I try to add new employee name and change the range to from $C$100 to $D$100 and :$C$1 to :$D$1 but it is not giving the desire result.

Can you advise what should i do in this case.

Regards
Sachin

 

[FormR]

I try to add new employee name and change the range to from $C$100 to $D$100 and :$C$1 to :$D$1

Those amendments look OK.

Excel 2013/2016
	A	B	C
1	Date	Name	Count
2	09/01/2018	Sachin	2
3	09/01/2018	Vikas	4
4	09/01/2018	Another	3
5	09/01/2018	someone else	2
Sheet2

Cell Formulas
Range	Formula
C2	=COUNTIFS(INDEX(Sheet1!$B$2:$E$100,0,MATCH(B2,Sheet1!$B$1:$E$1,0)),"PL",Sheet1!$A$2:$A$100,"<"&A2)

Excel 2013/2016
	A	B	C	D	E
1	Date	Sachin	Vikas	Another	Someone Else
2	01/01/2018	-	-	PL
3	02/01/2018	PL	-
4	03/01/2018	PL		PL
5	04/01/2018
6	05/01/2018		PL		PL
7	06/01/2018		PL		PL
8	07/01/2018		PL
9	08/01/2018		PL	PL
10	09/01/2018				PL
11	10/01/2018	PL			PL
12	11/01/2018	PL		PL	PL
13	12/01/2018	PL	PL
Sheet1

 

[sachin99]

Hi ,


[TABLE="width: 305"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Sachin[/TD]
[TD]Vikas[/TD]
[TD]Sunil[/TD]
[TD]Raj[/TD]
[/TR]
[TR]
[TD]01-Jan-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]02-Jan-18[/TD]
[TD]PL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]03-Jan-18[/TD]
[TD]PL[/TD]
[TD] [/TD]
[TD]PL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]04-Jan-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]05-Jan-18[/TD]
[TD] [/TD]
[TD]PL[/TD]
[TD] [/TD]
[TD]PL[/TD]
[/TR]
[TR]
[TD]06-Jan-18[/TD]
[TD] [/TD]
[TD]PL[/TD]
[TD] [/TD]
[TD]PL[/TD]
[/TR]
[TR]
[TD]07-Jan-18[/TD]
[TD] [/TD]
[TD]PL[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]08-Jan-18[/TD]
[TD] [/TD]
[TD]PL[/TD]
[TD]PL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]09-Jan-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PL[/TD]
[/TR]
[TR]
[TD]10-Jan-18[/TD]
[TD]PL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PL[/TD]
[/TR]
[TR]
[TD]11-Jan-18[/TD]
[TD]PL[/TD]
[TD] [/TD]
[TD]PL[/TD]
[TD]PL[/TD]
[/TR]
[TR]
[TD]12-Jan-18[/TD]
[TD]PL[/TD]
[TD]PL[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13-Jan-18[/TD]
[TD] [/TD]
[TD]PL[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


Below is the output of all formulas


[TABLE="width: 342"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Employee[/TD]
[TD]Next PL[/TD]
[TD]Count Of PL[/TD]
[TD]Actual PL Count[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Vikas[/TD]
[TD="align: right"]05-Jan-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sachin[/TD]
[TD="align: right"]02-Jan-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sunil[/TD]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sher[/TD]
[TD="align: right"]05-Jan-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]


Count of PL "=COUNTIFS(INDEX(Sheet1!$B$2:$E$100,0,MATCH(B2,Sheet1!$B$1:$E$1,0)),"PL",Sheet1!$A$2:$A$100,"<"&A2)"

The formula is not giving correct output as from next PL there are 4 PL for Vikas and 3 for Sachin and so on.
Can you check and advise is i am doing anything wrong or there is issue with formula.

Regards

 

[sachin99]

Hi ,

Thanks for the formula,

i tried to update the formula in but still it is not giving the desire output to me.. can you share a excel file with me with formula.

Thanks !..

Regards
Sachin

 

[FormR]

can you share a excel file with me with formula

Hi, what you can see in post 7 is a screen shot of the Excel file with the formula implemented.

still it is not giving the desire output to me..

What output are you getting?
- Is it an error? If so what is the error?
- Is it simply the wrong result? If so, can you post example data that demonstrates this and state what the correct result should be?
- Is it something else? If so, what are the details?

If your layout is different to post 7 and you have attempted to alter the formula to your actual set-up then post the formula that you are trying and describe in detail your exact layout.