Check if leave dates clash with special duty

[Ashtagon]

I have a table with the following columns:

* Name
* Special duty role
* On special duty at [time/weekday combo]

And another table with the following

* Name
* On leave from [date]
* On leave until [date]

I want to create a formula on the second sheet that will check if the person's leave dates will clash with the date/timeslots in which that person is on special duty. Instinct tells me this is a vlookup() or index() function, but the exact formula is likely rather complex.

Help?

 

[Akashwani]

Hi Ashtagon,

Welcome to MrExcel.

I think the solution to your problem would be easier to find if you could split the time and weekday into 2 columns, is this possible?

Excel Workbook
	A	B	C	D
1	Name	Special Duty Role	Date	Time
2	Bill	Duty1	01/05/2011	06:00
3	Tom	Duty2	02/05/2011	08:00
4	Bob	Duty3	03/05/2011	10:00
5	Pat	Duty4	04/05/2011	09:00
6	Jan	Duty5	05/05/2011	14:00
7	Jon	Duty6	06/05/2011	12:00
8	Jim	Duty7	07/05/2011	18:00
9	Tim	Duty8	08/05/2011	04:00
10	Pat	Duty1	02/06/2011	06:00
11	Jan	Duty2	03/06/2011	08:00
12	Jon	Duty3	04/06/2011	10:00
13	Jim	Duty4	05/06/2011	09:00
14	Tim	Duty5	06/06/2011	14:00
15	Bill	Duty6	07/06/2011	12:00
16	Tom	Duty7	08/06/2011	18:00
17	Bob	Duty8	09/06/2011	04:00
Sheet1

You would then probably need an Index/Match with multiple criteria to return the Duty.

Excel Workbook
	A	B	C	D
1	Name	On Leave From	On Leave Until	Special Duty Role
2	Bill	28/04/2011	02/05/2011	Duty1
3	Tom	05/05/2011	06/05/2011	*
4	Bob	09/05/2011	02/06/2011	*
5	Pat	02/06/2011	07/06/2011	Duty4
6	Jan	03/06/2011	04/06/2011	Duty2
7	Jon	20/06/2011	27/06/2011	*
8	Jim	08/08/2011	28/08/2011	*
9	Tim	04/06/2011	12/06/2011	Duty5
Sheet2

I don't have a formula solution for you, but if you can alter your data I'm sure someone will provide one now this has been bumped up.

Good luck.

Ak

 

[Akashwani]

Hi Ashtagon,

In the absence of any other replies, will this work for you?.....

Excel Workbook
	A	B	C	D	E	F
1	Name	On Leave From	On Leave Until	Special Duty Role	Date	Check
2	Bill	28/04/2011	02/05/2011	Duty1	01/05/2011	1
3	Tom	05/05/2011	06/05/2011	#N/A	#N/A	0
4	Bob	09/05/2011	02/06/2011	#N/A	#N/A	0
5	Pat	02/06/2011	07/06/2011	Duty1	02/06/2011	1
6	Jan	03/06/2011	04/06/2011	Duty2	03/06/2011	1
7	Jon	20/06/2011	27/06/2011	#N/A	#N/A	0
8	Jim	08/08/2011	28/08/2011	#N/A	#N/A	0
9	Tim	04/06/2011	12/06/2011	Duty5	06/06/2011	1
Sheet2

The formula in D2 needs to be entered using ctrl shift enter NOT enter.
You can then copy it across to E2 and then down as far as you require.
The formula in F2 needs to be copied down, this is used for Conditional Formatting to Highlight the row if a date falls between the Leave dates.
The CF formula is =$F2>0

There is probably a better solution, but I hope this helps.

Ak