Next Leave date

S

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.

[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]
 
Sort by date Sort by votes
sachin99 said:
output Sample 1

if i updated date as 01-Jan-2018 then below will be the output
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Name
[/TD]
[TD]Next PL Date
[/TD]
[TD]Count of PL
[/TD]
[/TR]
[TR]
[TD]01-Jan-2018
[/TD]
[TD]Vikas
[/TD]
[TD]02-01-2018
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]01-Jan-2018
[/TD]
[TD]Sachin
[/TD]
[TD]05-01-2018
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]01-Jan-2018
[/TD]
[TD]Sunil
[/TD]
[TD]01-01-2018
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]01-Jan-2018
[/TD]
[TD]Umesh
[/TD]
[TD]05-01-2018
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Umesh only has 2 PL's in the calendar table in total, where does the expected result of 3 come from?

For each sample repost the calendar table and highlight (different colour font) which PL's are getting counted for each person.
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi, I think it would help if you did this..

FormR said:
For each sample repost the calendar table and highlight (different colour font) which PL's are getting counted for each person.
Click to expand...
 
Upvote 0
Re: Next Leave date
Hi ,

Below is the sample data.

scenario 1:

below is calendar table for scenario 1


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

​



output Sample 1

if i updated date as 01-Jan-2018 then below will be the output (PL consider are highlighted in bold and Blue)

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Next PL Date[/TD]
[TD]Count of PL[/TD]
[/TR]
[TR]
[TD]01-Jan-2018[/TD]
[TD]Vikas[/TD]
[TD]02-01-2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]01-Jan-2018[/TD]
[TD]Sachin[/TD]
[TD]05-01-2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]01-Jan-2018[/TD]
[TD]Sunil[/TD]
[TD]03-01-2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]01-Jan-2018[/TD]
[TD]Umesh[/TD]
[TD]11-01-2018[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]



Scenario 2 :

below is calendar table for 2nd example


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





Output sample 2

if i updated date as 08-Jan-2018 then below will be the output (PL consider are highlighted in bold and Red)

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Next PL Date[/TD]
[TD]Count of PL[/TD]
[/TR]
[TR]
[TD]08-Jan-2018[/TD]
[TD]Vikas[/TD]
[TD]09-01-2018[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]08-Jan-2018[/TD]
[TD]Sachin[/TD]
[TD]11-01-2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]08-Jan-2018[/TD]
[TD]Sunil[/TD]
[TD]09-01-2018[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]08-Jan-2018[/TD]
[TD]Umesh[/TD]
[TD]11-01-2018[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]



Do let me know if you have any query.

Regards
Sachin
 
Upvote 0
Hi, I see that you have completely changed your expected results for "Output Sample 2" - And the formula in post 14 gives those expected results.

You need to find a way to describe what you want to count and why or provide some sample data that clearly demonstrates it.

Can you understand why this is confusing?
 
Upvote 0
Hi

Yes, i have change the data but used the same data for both the examples with color coding which show which PL are consider for counting.


Thanks!!

Regards
 
Upvote 0
Here is my suggestion.


Excel 2013/2016
ABCDE
1DateEmployeeNext PLNext BlankCount Of PL
201/01/2018Vikas02/01/201804/01/20182
301/01/2018Sachin05/01/201807/01/20182
401/01/2018Sunil03/01/201804/01/20181
501/01/2018Umesh11/01/201813/01/20182
608/01/2018Vikas09/01/201812/01/20183
708/01/2018Sachin11/01/201813/01/20182
808/01/2018Sunil09/01/201813/01/20184
908/01/2018Umesh11/01/201813/01/20182
Sheet2
Cell Formulas
RangeFormula
C2=INDEX(Sheet1!$A$2:$A$100,MATCH(1,INDEX((Sheet1!$A$2:$A$100>=A2)*(INDEX(Sheet1!$B$2:$E$100,0,MATCH(B2,Sheet1!$B$1:$E$1,0))="PL"),0),0))
D2=INDEX(Sheet1!$A$2:$A$100,MATCH(1,INDEX((Sheet1!$A$2:$A$100>=C2)*(INDEX(Sheet1!$B$2:$E$100,0,MATCH(B2,Sheet1!$B$1:$E$1,0))=""),0),0))
E2=MATCH(D2,Sheet1!$A$2:$A$16,0)-MATCH(C2,Sheet1!$A$2:$A$16,0)



Excel 2013/2016
ABCDE
1DateVikasSachinSunilUmesh
201/01/2018
302/01/2018PL
403/01/2018PLPL
504/01/2018
605/01/2018PL
706/01/2018PL
807/01/2018
908/01/2018
1009/01/2018PLPL
1110/01/2018PLPL
1211/01/2018PLPLPLPL
1312/01/2018PLPLPL
1413/01/2018
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
VBA Find Cell from Date listed, in Columns, Return cell to highlight in color
Replies
7
Views
532
mumps
mumps
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
570
mrshuck
M
Vintage79
  • Solved
Count consecutive days and gaps between days
Replies
2
Views
218
Vintage79
Vintage79
S
  • Solved
Count of active employees between dates.
Replies
3
Views
723
Fluff
Fluff
I
  • Question Question
Unable to run VBA code
Replies
6
Views
165
RoryA
RoryA

Forum statistics

Threads
1,224,833
Messages
6,181,242
Members
453,026
Latest member
cknader

Share this page

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