• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vacation [SOLVED]

dawa

dawa

Member
[pre]
Code: 
Emp_no	name	Vacation date	End date	return to work
81135	dawa	26-Jan-13	2/15/2013	2/16/2013
81136	balaram	28-Jan-13	2/12/2013	2/13/2013
81137	hari	29-Jan-13	2/8/2013	2/9/2013
81138	giri	2-Feb-13	3/4/2013	3/5/2013
81135	dawa	12-Mar-13	3/27/2013	3/28/2013
81137	hari	16-Mar-13	3/31/2013	4/1/2013
81139	janak	27-Mar-13	4/11/2013	4/12/2013
81138	giri 	12-Apr-13	4/27/2013	4/28/2013
81137	hari	4-May-13	5/19/2013	5/20/2013
[/pre]

this is the sheet that i have, what i want is when i type in the employment no, the formula will just extract the latest date of the employee vacation start and end date. there are some employee who has taken more than one vacation in a year. so the formula will just ignore the old dates and give me the latest date of his vacation.
 
Hi Dawa,


Assuming your data in A1:D10 try this:


Code: 
=MAX(IF($A$2:$A$10=81135,$C$2:$C$10)) Press Ctrl+Shift+Enter to execute

=MAX(IF($A$2:$A$10=81135,$D$2:$D$10))
Press Ctrl+Shift+Enter to execute


You can replace 81135 with cell reference.


Regards,
 
Thanks for the prompt reply...you are awesome. Why do we have to Press Ctrl+Shift+Enter to execute?
 
Serial # 265 266 1 1 1 1 1 1

Designation DC Assist DC Assist DC Assist DC Assist DC Assist DC Assist DC Assist DC Assist

Division Warehouse Warehouse Warehouse Warehouse Warehouse Warehouse Warehouse Warehouse

Empl # 80459 80740 80717 80911 80875 80847 80931 81098

Week Date Day Status Haridas MP Kumar Udas Mohd Rahin Tahir Khan Ayoob ali Hashmi Gul Shahbaz Prasant Panicker Faizal Thanthan Thodiyil

Week 2 8-Jan-13 WD 8 SDO 8 8 8 8 8 8

Week 2 9-Jan-13 WD 8 8 8 8 8 8 8 8

Week 2 10-Jan-13 WD 8 8 8 8 8 8 8 8

Week 3 11-Jan-13 WD SDO SDO SDO SDO SDO SDO SDO SDO

Week 3 12-Jan-13 WD 8 8 8 8 8 8 8 8

Week 3 13-Jan-13 WD 8 8 8 8 8 8 8 8

Week 3 12-Jan-13 WD 8 8 8 8 8 8 8 8

Week 3 15-Jan-13 WD 8 8 8 8 8 8 8 8

Week 3 16-Jan-13 WD 8 8 8 8 8 8 8 8

Week 3 17-Jan-13 WD 8 8 8 8 8 8 8 8

Week 4 18-Jan-13 WD SDO SDO SDO SDO SDO SDO SDO SDO

Week 4 19-Jan-13 WD 8 8 8 8 8 8 8 8

Week 4 20-Jan-13 WD 8 8 8 8 8 SL 8 8

Week 4 21-Jan-13 WD 8 8 8 8 8 SL 8 8

Week 4 22-Jan-13 WD 8 8 8 8 8 SL 8 8

Week 4 23-Jan-13 WD 8 8 8 8 8 8 8 8

Week 4 24-Jan-13 PH PH PH PH PH PH PH PH PH

Week 5 25-Jan-13 WD SDO SDO SDO SDO SDO SDO SDO SDO

Week 5 26-Jan-13 WD 8 8 8 8 8 8 8 8

Week 5 27-Jan-13 WD 8 8 8 8 8 8 8 8

Week 5 28-Jan-13 WD 8 8 8 SL 8 8 8 UL

Week 5 29-Jan-13 WD 8 8 8 SL 8 8 8 UL

Week 5 30-Jan-13 WD 8 8 8 8 8 8 8 UL

Week 5 31-Jan-13 WD 8 8 8 8 8 8 8 8

Week 6 1-Feb-13 WD SDO SDO SDO SDO SDO SDO SDO SDO

Week 6 2-Feb-13 WD 8 8 8 8 8 8 8 8

Week 6 3-Feb-13 WD 8 8 8 8 8 8 8 8

Week 6 4-Feb-13 WD 8 8 SL 8 8 8 8 8

Week 6 5-Feb-13 WD 8 8 SL 8 8 8 8 8

Week 6 6-Feb-13 WD 8 8 8 8 8 8 8 8

Week 6 7-Feb-13 WD 8 8 8 8 8 8 8 8

Week 7 8-Feb-13 WD SDO SDO SDO SDO SDO SDO SDO SDO

Week 7 9-Feb-13 WD 8 8 8 8 8 8 8 8

Week 7 10-Feb-13 WD 8 8 8 8 8 8 8 8

Week 7 11-Feb-13 WD 8 8 8 8 8 8 8 8

Week 7 12-Feb-13 WD 8 8 8 8 8 8 8 8

Week 7 13-Feb-13 WD 8 8 8 8 8 8 8 8

Week 7 12-Feb-13 WD 8 8 8 8 8 8 8 8

Week 8 15-Feb-13 WD 8 SDO 8 8 8 8 8 8

Week 8 16-Feb-13 WD SDO 8 SDO SDO SDO SDO SDO SDO

Week 8 17-Feb-13 WD 8 8 8 8 8 8 8 8

Week 8 18-Feb-13 WD 8 8 8 8 8 8 8 8

Week 8 19-Feb-13 WD 8 8 8 8 8 8 8 8

Week 8 20-Feb-13 WD 8 8 8 8 8 8 8 8

Week 8 21-Feb-13 WD 8 8 8 8 8 8 8 8

Week 9 22-Feb-13 WD SDO SDO SDO SDO SDO SDO SDO SDO

Week 9 23-Feb-13 WD 8 8 8 8 8 8 8 8

Week 9 24-Feb-13 WD 8 8 8 8 8 8 8 8

Week 9 25-Feb-13 WD 8 8 8 8 8 8 8 8

Week 9 26-Feb-13 WD 8 8 8 8 8 8 8 8

Week 9 27-Feb-13 WD 8 8 8 8 8 8 8 8

Week 9 28-Feb-13 WD 8 8 8 8 8 8 8 8

Week 10 1-Mar-13 WD SDO SDO SDO SDO SDO SDO SDO SDO

Week 10 2-Mar-13 WD 8 8 8 8 8 8 8 8

Week 10 3-Mar-13 WD 8 8 8 8 8 8 8 8

Week 10 4-Mar-13 WD 8 8 8 8 SL 8 8 8

Week 10 5-Mar-13 WD 8 8 8 8 SL 8 8 8

Week 10 6-Mar-13 WD 8 8 8 8 8 8 8 8

Week 10 7-Mar-13 WD 8 8 8 8 8 8 8 8

Week 11 8-Mar-13 WD SDO SDO SDO SDO SDO SDO SDO SDO

Week 11 9-Mar-13 WD AL 8 8 8 8 8 8 8

Week 11 10-Mar-13 WD AL 8 8 8 8 8 8 8

Week 11 11-Mar-13 WD AL 8 8 8 8 8 8 8

Week 11 12-Mar-13 WD AL 8 8 8 8 8 8 8

Week 11 13-Mar-13 WD AL 8 8 8 8 8 8 8

Week 11 12-Mar-13 WD AL 8 8 8 8 AL 8 8

Week 12 15-Mar-13 WD AL SDO SDO SDO SDO SDO SDO SDO

Week 12 16-Mar-13 WD AL 8 AL 8 8 8 8 8

Week 12 17-Mar-13 WD AL 8 AL 8 8 8 8 8

Week 12 18-Mar-13 WD AL 8 AL 8 8 8 8 8

Week 12 19-Mar-13 WD AL 8 AL 8 8 8 8 8

Week 12 20-Mar-13 WD AL 8 AL 8 AL 8 8 8

Week 12 21-Mar-13 WD AL 8 AL 8 AL 8 8 8

Week 13 22-Mar-13 WD SDO SDO AL SDO AL SDO SDO SDO

Week 13 23-Mar-13 WD 8 8 AL 8 AL 8 8 8

Week 13 24-Mar-13 WD 8 8 AL 8 AL 8 8 8

Week 13 25-Mar-13 WD 8 8 AL 8 AL 8 8 8

Week 13 26-Mar-13 WD 8 8 AL 8 AL 8 8 10

Week 13 27-Mar-13 WD 8 8 AL 8 AL 8 8 8

Week 13 28-Mar-13 WD 8 8 AL 8 AL 8 8 8

Week 12 29-Mar-13 WD SDO SDO AL SDO AL SDO SDO SDO

Week 12 30-Mar-13 WD 8 8 AL 8 AL 8 8 SL

Week 12 31-Mar-13 WD 8 8 AL 8 AL 8 8 8

Week 12 1-Apr-13 WD 8 8 AL RG 8 8 8 8

Week 12 2-Apr-13 WD 8 8 AL RG 8 8 8 8

Week 12 3-Apr-13 WD 8 8 AL RG 8 8 8 8

Week 12 4-Apr-13 WD 8 8 AL RG 8 8 SL 8

Week 15 5-Apr-13 WD SDO SDO AL RG SDO SDO SDO SDO

Week 15 6-Apr-13 WD 8 8 AL RG 8 8 8 8

Week 15 7-Apr-13 WD 8 8 AL RG 8 8 8 8

Week 15 8-Apr-13 WD 8 8 AL RG 8 8 8 8

Week 15 9-Apr-13 WD 8 8 AL RG 8 8 8 8

Week 15 10-Apr-13 WD 8 8 AL RG 8 8 8 8

Week 15 11-Apr-13 WD 8 8 AL RG 8 8 8 8

Week 16 12-Apr-13 WD SDO SDO AL RG SDO SDO SDO SDO

Week 16 13-Apr-13 WD 8 SL AL RG 8 8 8 8

Week 16 12-Apr-13 WD 8 SL AL RG 8 8 8 8

Week 16 15-Apr-13 WD 8 8 8 RG 8 8 8 8

Week 16 16-Apr-13 WD 8 8 8 RG 8 8 8 8

Week 16 17-Apr-13 WD 8 8 8 RG 8 8 8 8

Week 16 18-Apr-13 WD 8 8 8 RG 8 8 8 8

Week 17 19-Apr-13 WD SDO SDO SDO RG SDO SDO SDO SDO

Week 17 20-Apr-13 WD 8 8 8 RG 8 8 8 8

Week 17 21-Apr-13 WD 8 8 8 RG SL 8 8 8

Week 17 22-Apr-13 WD 8 8 SL RG 8 8 8 8

Week 17 23-Apr-13 WD 8 8 SL RG 8 8 8 8

Week 17 24-Apr-13 WD 8 8 8 RG UL SL 8 8

Week 17 25-Apr-13 WD 8 8 8 RG 8 8 8 8

Week 18 26-Apr-13 WD SDO SDO SDO RG SDO SDO SDO SDO

Week 18 27-Apr-13 WD 8 8 8 RG 8 8 8 8

Week 18 28-Apr-13 WD 8 8 8 RG 8 8 8 8

Week 18 29-Apr-13 WD 8 8 8 RG 8 8 8 8

Week 18 30-Apr-13 WD 8 8 8 RG 8 8 8 8

Week 18 1-May-13 WD 8 8 8 RG 8 AL 8 8

Week 18 2-May-13 WD 8 8 8 RG 8 8 UL 8

Week 19 3-May-13 WD SDO SDO SDO RG SDO SDO SDO SDO

Week 19 4-May-13 WD 8 8 8 RG 8 8 8 8

Week 19 5-May-13 WD 8 8 8 RG 8 8 8 8

Week 19 6-May-13 WD 8 8 8 RG 8 8 8 8

Week 19 7-May-13 WD 8 8 8 RG 8 8 8 8

Week 19 8-May-13 WD 8 8 8 RG 8 8 8 8

Week 19 9-May-13 WD 8 8 8 RG 8 8 8 8

Week 20 10-May-13 WD SDO SDO SDO RG SDO SDO SDO SDO

Week 20 11-May-13 WD 8 8 8 RG 8 8 8 8

Week 20 12-May-13 WD 8 8 8 RG 8 8 8 8

Week 20 13-May-13 WD 8 8 8 RG 8 8 8 8

Week 20 12-May-13 WD 8 8 8 RG 8 8 8 8

Week 20 15-May-13 WD 8 8 8 RG 8 8 8 8

Week 20 16-May-13 WD 8 8 8 RG 8 8 8 8

Week 21 17-May-13 WD SDO SDO SDO RG SDO SDO SDO SDO

Week 21 18-May-13 WD 8 8 8 RG 8 8 AL 8

Week 21 19-May-13 WD 8 8 8 RG 8 8 AL 8

Week 21 20-May-13 WD 8 8 8 RG 8 8 AL 8

Week 21 21-May-13 WD 8 8 8 RG 8 SL AL 8

Week 21 22-May-13 WD 8 8 8 RG 8 SL AL 8

Week 21 23-May-13 WD 8 8 8 RG 8 8 AL 8

Week 22 24-May-13 WD SDO SDO SDO RG SDO SDO AL SDO

Week 22 25-May-13 WD 8 8 8 RG 8 8 AL 8

Week 22 26-May-13 WD 8 8 8 RG 8 8 AL 8

Week 22 27-May-13 WD 8 8 SL RG 8 8 AL 8

Week 22 28-May-13 WD 8 8 8 RG 8 8 AL 8

Week 22 29-May-13 WD 8 8 8 RG 8 8 AL 8

Week 22 30-May-13 WD 8 8 8 RG 8 8 AL 12

Week 23 31-May-13 WD 8 SDO SDO RG SDO SDO AL SDO

Week 23 1-Jun-13 WD 8 8 8 RG 8 8 AL 8

Week 23 2-Jun-13 WD 8 8 8 RG 8 8 AL 8

Week 23 3-Jun-13 WD 12 8 8 RG 8 8 AL 8

Week 23 4-Jun-13 WD 12 8 8 RG 8 8 AL 8

Week 23 5-Jun-13 WD 8 8 8 RG 8 8 AL 8

Week 23 6-Jun-13 PH PH PH PH RG PH PH AL PH

Week 24 7-Jun-13 WD SDO SDO SDO RG SDO SDO AL SDO

Week 24 8-Jun-13 WD 8 8 8 RG 8 8 AL 8

Week 24 9-Jun-13 WD 12 8 8 RG 8 8 AL 8

Week 24 10-Jun-13 WD 12 8 8 RG 8 8 AL UL

Week 24 11-Jun-13 WD 12 8 8 RG 8 8 AL 8

Week 24 12-Jun-13 WD 12 8 8 RG 8 8 AL 8

Week 24 13-Jun-13 WD 12 8 8 RG 8 8 AL 8

Week 25 14-Jun-13 WD 8 SDO SDO RG SDO SDO AL SDO

Week 25 15-Jun-13 WD 12 8 8 RG 8 UL AL 8

Week 25 16-Jun-13 WD 12 8 8 RG 8 8 AL AL

Week 25 17-Jun-13 WD 12 8 8 RG 8 8 AL AL

Week 25 18-Jun-13 WD 12 8 8 RG 8 8 AL AL

Week 25 19-Jun-13 WD 12 8 8 RG 8 8 AL AL

Week 25 20-Jun-13 WD 12 8 8 RG UL 8 AL AL

Week 26 21-Jun-13 WD SDO SDO SDO RG SDO SDO AL AL

Week 26 22-Jun-13 WD 8 8 8 RG 8 8 AL AL

Week 26 23-Jun-13 WD 8 8 8 RG 8 8 AL AL

Week 26 24-Jun-13 WD 12 8 8 RG 8 8 AL AL

Week 26 25-Jun-13 WD 8 8 8 RG 8 8 AL AL

Week 26 26-Jun-13 WD 8 8 8 RG 8 8 AL AL

Week 26 27-Jun-13 WD 12 8 8 RG 8 8 AL AL

Week 27 28-Jun-13 WD SDO SDO SDO RG SDO SDO AL AL

Week 27 29-Jun-13 WD 8 8 8 RG 8 8 AL AL

Week 27 30-Jun-13 WD 8 8 8 RG 8 8 AL AL

Week 27 1-Jul-13 WD 8 8 8 RG 8 8 AL AL

Week 27 2-Jul-13 WD 12 8 8 RG 8 8 8 AL

Week 27 3-Jul-13 WD 12 8 UL RG 8 8 8 AL

Week 27 4-Jul-13 WD 12 8 8 RG 8 8 8 AL

Week 28 5-Jul-13 WD SDO SDO SDO RG SDO SDO SDO AL

Week 28 6-Jul-13 WD 8 8 8 RG 8 8 8 AL

Week 28 7-Jul-13 WD 12 8 8 RG 8 8 8 AL

Week 28 8-Jul-13 WD 12 8 8 RG 8 8 8 AL

Week 28 9-Jul-13 WD 12 8 8 RG 8 8 8 AL

Week 28 10-Jul-13 WD 10 8 6 RG 6 6 10 AL

Week 28 11-Jul-13 WD 10 8 8 RG 8 8 8 8

Week 29 12-Jul-13 WD SDO SDO SDO RG SDO SDO 8 SDO

Week 29 13-Jul-13 WD 8 6 6 RG 6 6 8 UL

Week 29 14-Jul-13 WD 6 6 6 RG 6 6 8 UL

Week 29 15-Jul-13 WD 10 6 6 RG 6 6 10 UL

Week 29 16-Jul-13 WD 6 6 6 RG 6 6 8 OAL

Week 29 17-Jul-13 WD 6 6 6 RG 6 6 6 OAL

Week 29 18-Jul-13 WD 6 6 6 RG 6 6 6 OAL

Week 30 19-Jul-13 WD SDO SDO SDO RG SDO SDO SDO OAL

Week 30 20-Jul-13 WD 6 6 6 RG 6 6 6 OAL

Week 30 21-Jul-13 WD 6 6 6 RG 6 6 6 OAL

Week 30 22-Jul-13 WD 10 6 6 RG 6 6 8 OAL

Week 30 23-Jul-13 WD 10 6 6 RG 6 6 8 OAL

Week 30 24-Jul-13 WD 6 6 6 RG 6 6 6 OAL

Week 30 25-Jul-13 WD RG OAL

Week 31 26-Jul-13 WD RG OAL

Week 31 27-Jul-13 WD RG OAL

Week 31 28-Jul-13 WD RG OAL

Week 31 29-Jul-13 WD RG OAL

Week 31 30-Jul-13 WD RG OAL

Week 31 31-Jul-13 WD RG

Week 31 1-Aug-13 WD RG

Week 32 2-Aug-13 WD RG

Week 32 3-Aug-13 WD RG

Week 32 4-Aug-13 WD RG
Sick Leave 0 2 5 2 3 6 1 1

Unauthorised Leave 0 0 1 0 2 1 1 7

Annual Leave 13 0 30 0 12 2 45 25

Other Authorised Leave 0 0 0 0 0 0 0 15

Date of Resignation - - - - - - - -

Date of Termination - - - - - - - -

Date of Transfer - - - - - - - -

so what i want is when i put the employment no it will extract all the SL and UL with its corresponding week and date. I need this to find out the Sick leave and Unauthorized leave of a staffs within the six month rolling period.
 
Hi, dawa!


Better later than never, hence welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Instead of posting such a long data in this text box (with which maybe you're forcing people to build a model for trying to help you) consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!


PS: BTW, your other identical topic has been closed, so as to keep all the posts regarding the same topic within an unique thread in order to don't disperse information and contibutors efforts.


PS2: Starting a new cross posted topic or posting on a previously existing one without adding any useful information just for bumping it up doesn't guarantee you a better and faster response but exactly the opposite. Please read carefully and thoroughly the green sticky posts.
 
Hi, dawa!

Maybe you want to share your solution with the community or indicate which one you chose, so as people who read this would have the issue or question and the solution or answer as well.

Regards!
 
You must log in or register to reply here.
Back
Top