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

toalg

New Member
Joined
Dec 18, 2018
Messages
3
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.

1606135979413.png


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.

1606136042528.png


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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,408
Office Version
  1. 365
Platform
  1. MacOS
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
CD
1start dateend date
211/1/2011/10/20
311/5/2011/8/20
411/1/2011/10/20
511/3/2011/5/20
6
7
8
9
10
11
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:D21Expression= AND( $C2>$C1, $D2< $D1)textNO
 

toalg

New Member
Joined
Dec 18, 2018
Messages
3
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
CD
1start dateend date
211/1/2011/10/20
311/5/2011/8/20
411/1/2011/10/20
511/3/2011/5/20
6
7
8
9
10
11
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:D21Expression= AND( $C2>$C1, $D2< $D1)textNO


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

New Member
Joined
Dec 18, 2018
Messages
3
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,846
Messages
5,598,425
Members
414,237
Latest member
tereres

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top