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.

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>
 
Sort by date Sort by votes
Hi ,

There is no error but the count which i am getting is wrong below is the output which i am getting

DateNameNext PLCount of PLActual PL count
07-01-2018Vikas11-01-201821
07-01-2018Sachin09-01-201823
07-01-2018Sunil08-01-201822
07-01-2018Umesh09-01-201823

<tbody>
</tbody>

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



DateSachinVikasSunilUmesh
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
08-Jan-18 PL
09-Jan-18PL PLPL
10-Jan-18PL PL
11-Jan-18PLPL PL
12-Jan-18

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

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



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.
Click to expand...
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hi, looks like you want:

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

Which is different to the example you gave in post 4.
 
Upvote 0
FormR said:
Hi, looks like you want:

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


Which is different to the example you gave in post 4.
Click to expand...
----------------------------------------------------------------------------------

If i use this formula then it counts all PL from the given date..
Let me explain you my requirement again


1. I need the date on which PL is updated from date enter in cell A2 and if A2 is blank then it should consider todays date for calculation (Which your formula is giving correctly)


2. Need count of continues PLs from next PL date eg. if next PL Date for Sachin is 09-01-2018 and PL is updated till 11-Jan-2018 and 12-Jan is an empty cell then the PL count should be 3

Below is the table for your reference.

DateSachinVikasSunilUmesh
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
08-Jan-18 PL
09-Jan-18PL PLPL
10-Jan-18PL PL
11-Jan-18PLPL PL
12-Jan-18

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


The output Table

DateEmployeeNext PLCount Of PL
07-Jan-18Vikas11-Jan-181
07-Jan-18Sachin09-Jan-183
07-Jan-18Sunil08-Jan-182
07-Jan-18Umesh09-Jan-183


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

Apologies for any miscommunication ..

Thanks!!

Regards
Sachin
 
Upvote 0
sachin99 said:
2. Need count of continues PLs from next PL date eg.
Click to expand...

Hi, it's still not very clear to me, but maybe we should just be looking at the next PL date rather than the date? If not, post example data that demonstrates where that is not the case.


Excel 2013/2016
ABCD
1DateEmployeeNext PLCount Of PL
207/01/2018Vikas11/01/20181
307/01/2018Sachin09/01/20183
407/01/2018Sunil08/01/20182
507/01/2018Umesh09/01/20183
Sheet2
Cell Formulas
RangeFormula
D2=COUNTIFS(INDEX(Sheet1!$B$2:$E$100,0,MATCH(B2,Sheet1!$B$1:$E$1,0)),"PL",Sheet1!$A$2:$A$100,">="&C2)
 
Upvote 0
Hi

Good Day Everyone,

Below is my requirement to get next PL date and count of continues PL from the next PL date as shown in the below table and output table

Below is the table which shows date on which particular employee is on leave

​
DateSachinVikasSunilUmesh
01-Jan-2018PL
02-Jan-2018PL
03-Jan-2018PL
04-Jan-2018PL
05-Jan-2018
06-Jan-2018PLPL
07-Jan-2018PLPL
08-Jan-2018PL
09-Jan-2018PLPLPL
10-Jan-2018PLPL
11-Jan-2018PLPL
12-Jan-2018
13-Jan-2018
14-Jan-2018
15-Jan-2018PL
16-Jan-2018PL
17-Jan-2018PLPL
18-Jan-2018PLPL
19-Jan-2018PLPLPL
20-Jan-2018PLPLPL
21-Jan-2018PLPLPL
22-Jan-2018PLPL
23-Jan-2018PLPL
24-Jan-2018PLPL
25-Jan-2018PL

<tbody>
</tbody>












































Below is the output table if i update Date as 07-Jan-2018

DateEmployeeNext PL DateCount of PL
07-Jan-2018Sachin09-Jan-20183
07-Jan-2018VIkas15-Jan-20186
07-Jan-2018Sunil08-Jan-20182
07-Jan-2018Umesh09-Jan-20183

<tbody>
</tbody>










Below is the output table if i update Date as 07-Jan-2018

DateEmployeeNext PLCount of PL
14-Jan-2018Sachin21-Jan-20185
14-Jan-2018Vikas15-Jan-20186
14-Jan-2018Sunil19-Jan-20186
14-Jan-2018Umesh17-Jan-20185

<tbody>
</tbody>










These are the samples as the employee count will me 50-60.


Regards
Sachin
 
Upvote 0
FormR said:
Hi,

You can post screen shots using one of the methods described in post 2 here:
https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or you can continue to post example data in the same you already have been.[/QUOTE



  • I need a formula to search for next PL start date from the calender sheet of the attached file and in summary sheet will give desire details as per employee name

    Below is the calender table

    Date Vikas Sachin Sunil Umesh
    01-Jan-18 PL
    02-Jan-18 PL
    03-Jan-18 PL PL
    04-Jan-18
    05-Jan-18 PL PL
    06-Jan-18 PL PL
    07-Jan-18
    08-Jan-18 PL
    09-Jan-18 PL PL PL
    10-Jan-18 PL PL
    11-Jan-18 PL PL
    12-Jan-18
    13-Jan-18
    14-Jan-18
    15-Jan-18 PL
    16-Jan-18 PL
    17-Jan-18 PL PL
    18-Jan-18 PL PL
    19-Jan-18 PL PL PL
    20-Jan-18 PL PL PL
    21-Jan-18 PL PL PL
    22-Jan-18 PL PL
    23-Jan-18 PL PL
    24-Jan-18 PL
    25-Jan-18 PL


    Below is sample one when i update date as 07-Jan-2018 in date and below is the desire output

    Date Employee Next PL Count Of PL Example 1
    07-Jan-18 Vikas 09-Jan-18 3
    07-Jan-18 Sachin 15-Jan-18 6
    07-Jan-18 Sunil 08-Jan-18 2
    07-Jan-18 Umesh 09-Jan-18 3


    Below is sample two when i update date as 14-Jan-2018 in date and below is the desire output

    Date Employee Next PL Count Of PL Example 2
    14-Jan-18 Vikas 21-Jan-18 3
    14-Jan-18 Sachin 15-Jan-18 6
    14-Jan-18 Sunil 19-Jan-18 6
    14-Jan-18 Umesh 17-Jan-18 5

    so in both sample the count of PL is changes based on the date updated..

    Please note that the count of PL is number of PL in group Eg. have updated date as 07-Jan-2018 and in calener you can there are 3 PL updated from 9th to 11th Jan and then next PL is starting from 21-Jan .So the PL Count should be 3 for this .

    Regards
    Sachin​
Click to expand...
 
Upvote 0
That sample data has not posted well, can you try again?

Have you also posted this question to another forum?
 
Upvote 0
No , have not posted .. i just adding my comment on this thread

Hi ,

Below is the sample data.


below is calendar table

DateVikasSachinSunilUmesh
01-Jan-2018
02-Jan-2018PL
03-Jan-2018PLPL
04-Jan-2018
05-Jan-2018PL
06-Jan-2018PL
07-Jan-2018
08-Jan-2018
09-Jan-2018PLPL
10-Jan-2018PLPL
11-Jan-2018PLPLPLPL
12-Jan-2018PLPLPL
13-Jan-2018

<tbody>
</tbody>
























output Sample 1

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

DateNameNext PL DateCount of PL
01-Jan-2018Vikas02-01-20182
01-Jan-2018Sachin05-01-20182
01-Jan-2018Sunil01-01-20181
01-Jan-2018Umesh05-01-20183

<tbody>
</tbody>


Output sample 2

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

DateNameNext PL DateCount of PL
08-Jan-2018Vikas09-01-20182
08-Jan-2018Sachin11-01-20182
08-Jan-2018Sunil09-01-20181
08-Jan-2018Umesh11-01-20182

<tbody>
</tbody>



Thanks!!
Regards
Sachin
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
351
mrshuck
M
M
  • Solved
How to count certain month from a column
Replies
3
Views
109
Joe4
Joe4
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
218
B-radK
B
R
  • Question
Index Match Formula to retrieved next cell value
Replies
4
Views
254
Fluff
Fluff

Forum statistics

Threads
1,214,940
Messages
6,122,352
Members
449,080
Latest member
Armadillos

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