Next Leave date

sachin99

New Member
Joined
Jun 18, 2013
Messages
15
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.

DateSachinVikas
01-Jan-2018--
02-Jan-2018PL-
03-Jan-2018PL
04-Jan-2018
05-Jan-2018PL
06-Jan-2018PL
07-Jan-2018PL
08-Jan-2018PL
09-Jan-2018
10-Jan-2018PL
11-Jan-2018PL
12-Jan-2018PLPL

<tbody>
</tbody>

Regards
Sachin


<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,466
Office Version
  1. 365
Platform
  1. Windows
Hi, welcome to the board!

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

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Name</td><td style=";">Next PL</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">09/01/2018</td><td style=";">Sachin</td><td style="text-align: right;background-color: #FFFF00;;">10/01/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">09/01/2018</td><td style=";">Vikas</td><td style="text-align: right;;">12/01/2018</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet1!$A$2:$A$100,MATCH(<font color="Red">1,INDEX(<font color="Green">(<font color="Purple">Sheet1!$A$2:$A$100>=A2</font>)*(<font color="Purple">INDEX(<font color="Teal">Sheet1!$B$2:$C$100,0,MATCH(<font color="#FF00FF">B2,Sheet1!$B$1:$C$1,0</font>)</font>)="PL"</font>),0</font>),0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Sachin</td><td style=";">Vikas</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">01/01/2018</td><td style=";">-</td><td style=";">-</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">02/01/2018</td><td style=";">PL</td><td style=";">-</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">03/01/2018</td><td style=";">PL</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">04/01/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">05/01/2018</td><td style="text-align: right;;"></td><td style=";">PL</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">06/01/2018</td><td style="text-align: right;;"></td><td style=";">PL</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">07/01/2018</td><td style="text-align: right;;"></td><td style=";">PL</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">08/01/2018</td><td style="text-align: right;;"></td><td style=";">PL</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">09/01/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">10/01/2018</td><td style=";">PL</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">11/01/2018</td><td style=";">PL</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">12/01/2018</td><td style=";">PL</td><td style=";">PL</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />
 

Saneesh

New Member
Joined
Jun 15, 2017
Messages
5
Hi Sachin,

Try this

Sheet 2
A ColumnB ColumnC Column
NamesFrom DateNext PL leave
Sachin04-Jan-1801/12/2018
Vikas01-Jan-1801/12/2018

<tbody>
</tbody>



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

New Member
Joined
Jun 18, 2013
Messages
15
Hi ,

Thanks for quick and helpful reply...

One more addition to current thread...

can i get the number of leaves applied

Eg.

ABC
1DateNameNext PL
209/01/2018Sachin02/01/2018
309/01/2018Vikas05/01/2018

<colgroup><col width="25px" style="background-color: rgb(218, 231, 245);"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
For Sachin it is 2 and for Vikas it is 4.

Regards
Sachin
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,466
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi, like this maybe.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Name</td><td style=";">Next PL</td><td style=";">leaves applied</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">09/01/2018</td><td style=";">Sachin</td><td style="text-align: right;;">02/01/2018</td><td style="text-align: right;background-color: #FFFF00;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">09/01/2018</td><td style=";">Vikas</td><td style="text-align: right;;">05/01/2018</td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">INDEX(<font color="Red">Sheet1!$B$2:$C$100,0,MATCH(<font color="Green">B2,Sheet1!$B$1:$C$1,0</font>)</font>),"PL",Sheet1!$A$2:$A$100,"<"&A2</font>)</td></tr></tbody></table></td></tr></table><br />
 

sachin99

New Member
Joined
Jun 18, 2013
Messages
15
Hi, like this maybe.

Excel 2013/2016
ABCD
1DateNameNext PLleaves applied
209/01/2018Sachin02/01/20182
309/01/2018Vikas05/01/20184

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,466
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Name</td><td style=";">Count</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">09/01/2018</td><td style=";">Sachin</td><td style="text-align: right;background-color: #FFFF00;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">09/01/2018</td><td style=";">Vikas</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">09/01/2018</td><td style=";">Another</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">09/01/2018</td><td style=";">someone else</td><td style="text-align: right;;">2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">INDEX(<font color="Red">Sheet1!$B$2:$E$100,0,MATCH(<font color="Green">B2,Sheet1!$B$1:$E$1,0</font>)</font>),"PL",Sheet1!$A$2:$A$100,"<"&A2</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Sachin</td><td style=";">Vikas</td><td style=";">Another</td><td style=";">Someone Else</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">01/01/2018</td><td style=";">-</td><td style=";">-</td><td style=";">PL</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">02/01/2018</td><td style=";">PL</td><td style=";">-</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">03/01/2018</td><td style=";">PL</td><td style="text-align: right;;"></td><td style=";">PL</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">04/01/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">05/01/2018</td><td style="text-align: right;;"></td><td style=";">PL</td><td style="text-align: right;;"></td><td style=";">PL</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">06/01/2018</td><td style="text-align: right;;"></td><td style=";">PL</td><td style="text-align: right;;"></td><td style=";">PL</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">07/01/2018</td><td style="text-align: right;;"></td><td style=";">PL</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">08/01/2018</td><td style="text-align: right;;"></td><td style=";">PL</td><td style=";">PL</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">09/01/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">PL</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">10/01/2018</td><td style=";">PL</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">PL</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">11/01/2018</td><td style=";">PL</td><td style="text-align: right;;"></td><td style=";">PL</td><td style=";">PL</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">12/01/2018</td><td style=";">PL</td><td style=";">PL</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />
 

sachin99

New Member
Joined
Jun 18, 2013
Messages
15
Hi ,


DateSachinVikasSunilRaj
01-Jan-18 PL
02-Jan-18PL
03-Jan-18PL PL
04-Jan-18
05-Jan-18 PL PL
06-Jan-18 PL PL
07-Jan-18 PL
08-Jan-18 PLPL
09-Jan-18 PL
10-Jan-18PL PL
11-Jan-18PL PLPL
12-Jan-18PLPL
13-Jan-18 PL

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>


Below is the output of all formulas


DateEmployeeNext PLCount Of PLActual PL Count
Vikas05-Jan-1804
Sachin02-Jan-1803
Sunil01-Jan-1801
Sher05-Jan-1802

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>


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

New Member
Joined
Jun 18, 2013
Messages
15
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,466
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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
Top