Conditional Formatting, to highligh a date ranget, within a date range.

[toalg]

Good morning, I have a Vacation Tracker that I have been working on modifying and I have run into an issue. That issues is if an employee submits a request for vacation that falls within another request i would like to use conditional formatting it give me a visual cue to look at it further before approving.

Start date of Vacation would go in column "C" and end date in "D" shown below.

What I would like to be able to do is highlight the entry in Row 4 as it falls within the entry of row 3, the out come is shown below.

I have tried searching for an answer on google, but a lot of those solutions define the date range in else were and compare it to the date input. I guess this conditonal format needs to be slighlty more dynamic and that is sadly above my skill set.

Thanks in advance

 

[etaf]

are there multiple rows comparing ?
do you need both dates within range or just 1 overlap ie in your example 1st oct 20 to 2nd nov 20 OR 9th Nov to 20th Nov

you could use a countif() or AND
= AND( C4>C3 , D4 < D3 )
using $ and apply to the range
= AND( $C4>$C3 , $D4 < $D3 )

Book1
	C	D
1	start date	end date
2	11/1/20	11/10/20
3	11/5/20	11/8/20
4	11/1/20	11/10/20
5	11/3/20	11/5/20
6
7
8
9
10
11
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D21	Expression	= AND( $C2>$C1, $D2< $D1)	text	NO

 

[toalg]

are there multiple rows comparing ?
do you need both dates within range or just 1 overlap ie in your example 1st oct 20 to 2nd nov 20 OR 9th Nov to 20th Nov

you could use a countif() or AND
= AND( C4>C3 , D4 < D3 )
using $ and apply to the range
= AND( $C4>$C3 , $D4 < $D3 )

Book1
	C	D
1	start date	end date
2	11/1/20	11/10/20
3	11/5/20	11/8/20
4	11/1/20	11/10/20
5	11/3/20	11/5/20
6
7
8
9
10
11
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D21	Expression	= AND( $C2>$C1, $D2< $D1)	text	NO

there will be multiple rows being compared, I dont need both dates compared. I would only be interesed to know if another start date fell within an already scheduled time frame. if that makes sense

 

[toalg]

I think possibly a better description of the issue i am facing.

What I want to do is to be able to place a start date in the column labeled as such. I want to be able to then say " If that newly inputed start date falls within an already specified range (Start date , End Date columns) then that newly inputted Start date should show red. This is to try and prevent giving vacation to team members if another team member is off work. does that clarify things any?