Comparing two lists of date ranges based on name

[al02847]

I am trying to organise an on call roster where an individual is selected for a seven day period. This is in a table with name - start date - end date of when they are 'on call'.

In a second sheet, I want a second table where people input the dates they are unavailable to be on call due to annual leave etc. It will also be in a table with name - start date - end date of when they are unavailable.

In a fourth column in the roster table - I want a formula that will automatically say 'Unavailable' when someone is selected to go on call that clashes with any of the dates they have put in the unavailable table.

In this scenario, Steven would be unavailable to be on call on 06/01/23 - 13/01/23 because it clashes with his annual leave from 02/01/23 - 08/01/23.

I can find answers on how to compare two date ranges, but I can't find a way to check against all the dates in the 'unavailable' table against each person's name.

Thanks if anyone can help

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C
1
2	c	01/01/2023	01/01/2023
3	a	08/01/2023	09/01/2023
4	b	02/01/2023	08/01/2023
5
Sheet2

Fluff.xlsm
	A	B	C	D
1
2	a	30/12/2022	06/01/2023
3	b	06/01/2023	13/01/2023	Unavailable
4	c	13/01/2023	20/01/2023
5
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"<="&C2,Sheet2!C:C,">="&B2),"Unavailable","")

 

[al02847]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C
1
2	c	01/01/2023	01/01/2023
3	a	08/01/2023	09/01/2023
4	b	02/01/2023	08/01/2023
5
Sheet2

Fluff.xlsm
	A	B	C	D
1
2	a	30/12/2022	06/01/2023
3	b	06/01/2023	13/01/2023	Unavailable
4	c	13/01/2023	20/01/2023
5
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"<="&C2,Sheet2!C:C,">="&B2),"Unavailable","")

Hello and thank you for your quick response! This works great thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.