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.

Date	Sachin	Vikas
01-Jan-2018	-	-
02-Jan-2018	PL	-
03-Jan-2018	PL
04-Jan-2018
05-Jan-2018		PL
06-Jan-2018		PL
07-Jan-2018		PL
08-Jan-2018		PL
09-Jan-2018
10-Jan-2018	PL
11-Jan-2018	PL
12-Jan-2018	PL	PL

<tbody>
</tbody>

Regards Sachin

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

 

[FormR]

output Sample 1

if i updated date as 01-Jan-2018 then below will be the output

Date	Name	Next PL Date	Count of PL
01-Jan-2018	Vikas	02-01-2018	2
01-Jan-2018	Sachin	05-01-2018	2
01-Jan-2018	Sunil	01-01-2018	1
01-Jan-2018	Umesh	05-01-2018	3

<tbody>
</tbody>

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.

 

[sachin99]

Hi,

it was typo error to update 3 instead of 2 PL

Thanks!!

Regards
Sachin

 

[FormR]

Hi, I think it would help if you did this..

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

 

[sachin99]

Re: Next Leave date

Hi ,

Below is the sample data.

scenario 1:

below is calendar table for scenario 1

Date	Vikas	Sachin	Sunil	Umesh
01-Jan-2018
02-Jan-2018	PL
03-Jan-2018	PL		PL
04-Jan-2018
05-Jan-2018		PL
06-Jan-2018		PL
07-Jan-2018
08-Jan-2018
09-Jan-2018	PL		PL
10-Jan-2018	PL		PL
11-Jan-2018	PL	PL	PL	PL
12-Jan-2018		PL	PL	PL
13-Jan-2018

<tbody>
</tbody>

​

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)

Date	Name	Next PL Date	Count of PL
01-Jan-2018	Vikas	02-01-2018	2
01-Jan-2018	Sachin	05-01-2018	2
01-Jan-2018	Sunil	03-01-2018	1
01-Jan-2018	Umesh	11-01-2018	2

<tbody>
</tbody>

Scenario 2 :

below is calendar table for 2nd example

Date	Vikas	Sachin	Sunil	Umesh
01-Jan-2018
02-Jan-2018	PL
03-Jan-2018	PL		PL
04-Jan-2018
05-Jan-2018		PL
06-Jan-2018		PL
07-Jan-2018
08-Jan-2018
09-Jan-2018	PL		PL
10-Jan-2018	PL		PL
11-Jan-2018	PL	PL	PL	PL
12-Jan-2018		PL	PL	PL
13-Jan-2018

<tbody>
</tbody>

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)

Date	Name	Next PL Date	Count of PL
08-Jan-2018	Vikas	09-01-2018	3
08-Jan-2018	Sachin	11-01-2018	2
08-Jan-2018	Sunil	09-01-2018	4
08-Jan-2018	Umesh	11-01-2018	2

<tbody>
</tbody>

Do let me know if you have any query.

Regards
Sachin

 

[FormR]

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?

 

[sachin99]

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

 

[FormR]

Here is my suggestion.

Excel 2013/2016
	A	B	C	D	E
1	Date	Employee	Next PL	Next Blank	Count Of PL
2	01/01/2018	Vikas	02/01/2018	04/01/2018	2
3	01/01/2018	Sachin	05/01/2018	07/01/2018	2
4	01/01/2018	Sunil	03/01/2018	04/01/2018	1
5	01/01/2018	Umesh	11/01/2018	13/01/2018	2
6	08/01/2018	Vikas	09/01/2018	12/01/2018	3
7	08/01/2018	Sachin	11/01/2018	13/01/2018	2
8	08/01/2018	Sunil	09/01/2018	13/01/2018	4
9	08/01/2018	Umesh	11/01/2018	13/01/2018	2
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:$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
	A	B	C	D	E
1	Date	Vikas	Sachin	Sunil	Umesh
2	01/01/2018
3	02/01/2018	PL
4	03/01/2018	PL		PL
5	04/01/2018
6	05/01/2018		PL
7	06/01/2018		PL
8	07/01/2018
9	08/01/2018
10	09/01/2018	PL		PL
11	10/01/2018	PL		PL
12	11/01/2018	PL	PL	PL	PL
13	12/01/2018		PL	PL	PL
14	13/01/2018
Sheet1

 

[sachin99]

Hi ,

Thanks a lot for your assistance. Have nice day ahead....


Regards
Sachin