Find the last date of Annual Leave for person

[asad]

Hello Guys,

I got two files - one is for rostering, and the other one is for leave allocation and record of leave.

In the rostering file, I got a formula that tells me whether a person is on leave for the current week or not. If he is on leave, his name will get highlighted in yellow. To make things easier, I am posting a portion of the file here:

Excel Workbook
	A	B	C	D	E	F
7	0		1		MATAU
8	A/L		2		REEVE	A/L 15/05/11
Weekly Roster

Now, as you can see I have inserted a date in cell F8 showing the last date for this person to be on leave. Is there a formula that I can use to put this date by itself?

I will show the other file here as well to give you better idea to work out the formula.

Excel Workbook
	J	K	L	M	N	O
2	Name		2010
3		28-Dec	4-Jan	11-Jan	18-Jan	25-Jan
4		3-Jan	10-Jan	17-Jan	24-Jan	31-Jan
5	1	2	3	4	5	6
6	Both Depots Total	45	46	44	42	17
7	Sandringham Total	4	5	5	4	2
8	Balance + or - on Leave	#REF!	#REF!	#REF!	#REF!	#REF!
9	Footscray on LeaveTotal	41	41	39	38	15
10	AM Drivers on Leave	22	22	20	20	8
11	Relief Drivers on Leave	1	1	1	0	0
12	Balance + or - on Leave	#REF!	#REF!	#REF!	#REF!
13	PM Drivers on Leave	18	18	18	18	7
14	Sandringham Total	4	5	5	4	2
15	ADDICOTT
16	AGGETT
Leave Approved

Any help would be greatly appreciated.

Asad

 

[asad]

Another thing I forgot to mention is that it is quite usual for a person to have 2, or 3 or more lots of holidays in a year and hence the file will may have any number of blocks for a person. I need the formula to look at the block of holidays which will be represented by 1s in adjascent cells in a row under the relevant dates (dates as you can see in my previous post are week beginning date - Monday, and week ending date - Sunday).

 

[asad]

I tried this formula but it gives me a message to check for "-" or "=" signs . The formula I made is like this:

IF(A8 < > $A$5,"",min(IF(len(columns(INDEX('[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$FP$201,MATCH($E8,'[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$J$201,0),MATCH($BB$1,'[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$FP$4,0))):index('[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$FP$201,MATCH($E8,'[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$J$201,0),163)) < 0,index('[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$FP$4,1,columns(INDEX('[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$FP$201,MATCH($E8,'[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$J$201,0),MATCH($BB$1,'[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$FP$4,0))):index('[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$FP$201,MATCH($E8,'[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$J$201,0),163)))))

 

[asad]

I worked out the following formula.

=IF($A8 < > $A$5,"",INDEX('[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$A$4:$FP$4,1,MIN(IF(LEN(INDEX('[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$FP$201,MATCH($E8,'[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$J$201,0),MATCH($BB$1,'[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$FP$4,0)):INDEX('[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$FP$201,MATCH($E8,'[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$J$201,0),163))=0,COLUMN(INDEX($J$4:$FP$4,1,MATCH($BB$1,'[Leave  allocation 2011 MasterTrial.xls]Leave Approved'!$J$4:$FP$4,0)):$FP$4)-1,FALSE))))

It is working.
Is it possible to shorten it a bit?
Asad

 

[asad]

.