Highlight overlapping date ranges using conditional formatting
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that overlaps.
The first and fourth record are highlighted because the date ranges overlap. 1/4/2010 - 1/8/2010 overlaps with 1/7/2010 - 1/9/2010.
Here are the steps needed to create a conditional formatting formula
- Select cell range B6:D12.
- Press with left mouse button on "Home" tab.
- Press with left mouse button on "Conditional Formatting" button.
- Press with left mouse button on "New Rule..".
- Press with left mouse button on "Use a formula to determine which cells to format".
- Type =SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1 in "Format values where this formula is TRUE" window.
- Press with left mouse button on "Format.." button.
- Press with left mouse button on "Fill" tab.
- Pick a color. This color is used to highlight records that overlap.
- Press with left mouse button on "OK".
- Press with left mouse button on "OK".
- Press with left mouse button on "OK".
Explaining conditional formatting formula
=SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1
Step 1 - Filter records where ($C6<=$D$6:$D$12) is TRUE
$C6<=$D$6:$D$12 returns an array of TRUE and/or FALSE if date in cell C6 is smaller or equal to each date in $D$6:$D$12.
All dates in$D$6:$D$12 are larger than $C6 so the returning array is (TRUE, TRUE, TRUE,TRUE, TRUE, TRUE,TRUE)
Step 2 - Filter records where ($D6>=$C$6:$C$12) is TRUE
($D6>=$C$6:$C$12) returns an array of TRUE and/or FALSE if date in cell D6 is bigger or equal to each date in $C$6:$C$12.
The returning array is (TRUE, FALSE, FALSE,TRUE, FALSE, FALSE,FALSE)
Step 3 - Putting it all together
($C6<=$D$6:$D$12)* ($D6>=$C$6:$C$12)) returns the following array (1,0,0,1,0,0,0).
That means array number one and four is overlapping C6:D6) but in this case we just want to know if any date range is overlapping.
=SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12)) sums the array (1,0,0,1,0,0,0) and returns 2.
If the formula returns a number bigger than one there is at least one overlapping date range.
=SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1 returns TRUE or FALSE.
Recommended articles
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.
Get excel *.xls file
Cf dates category
Cell range B3:B14 has conditional formatting applied, the formula checks if the date is today. Conditional formatting formula: =TODAY()=B3 A […]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Explaining CF formula in cell B6 Step 1 - Calculate number of month The […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
The following conditional formatting formula highlights dates based on day of the week. =TEXT(B3,"DDDD")=$D$3 The TEXT function converts a value […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays […]
Conditional Formatting categories
Excel categories
31 Responses to “Highlight overlapping date ranges using conditional formatting”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi, I have a situation where I want to count if this value is duplicate and if it the dates are overlapping as well. Is there anyway to work this out?
eg
Serial | Start date | End Date
ABC | 01.01.2009 | 31.12.2009
BCD | 01.06.2009 | 31.12.2009
ABC | 01.07.2009 | 30.06.2010
So it should list the first and third entry.
[...] Highlight duplicate values and overlapping dates in excel Filed in Excel on Oct.09, 2010. Email This article to a Friend adam asks: [...]
Adam,
read this post: Highlight duplicate values and overlapping dates in excel
I want to do something similar but only if the value in column B is the same as the previous or subseqhent row. In other words, column B might be something like an employee number (5 in this case) and I only want to look for overlapping dates across the same employee number like this.
# Start date End date
5 2/11/1995 10/22/2002
5 10/22/2002 12/31/9999
The above overlap as does the below
5 2/11/1995 10/22/2002
5 10/19/2002 12/31/9999
Can I do this in excel with the condition that the employee number Key (5 in this case) has to match?
PJ,
Conditional formatting formula:
=SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12)*($B6=$B$6:$B$12))>1
Get the Excel file
Oscar,
I have i problem in how to compute the total interval of date and time .And i would like the output like this: 1(for day) 02:00(for hours). Please help......
Example.
A-start B. End C.Output
1/23/2012 01:00pm 1/24/2012 03:00pm 1 2:00
mei,
Formula in C2:
Oscar,
I try the formula but something error appear.
"The formula you typed contain an error"
Please oscar help me.......
Hi Oscar,
I try the formula but something error appear.
"The formula you typed contain an error" this is the error appeared in my sheet.
Please help
mei,
You may have to adjust the "HH:MM" part depending on your regional settings. HH is hours and MM is minutes.
Oscar,
Thank you so much.....
Hello Oscar,
thank you so much for your formula, it has helped me a lot so far.
i work for a small car rental company, and im having another problem with applying your code.
i will try to explain it as clearly as possible without having to share the complete file.
I have a table that collapses column's.
e.g.
Vehicle License plate - Chauffeur - Date out - Date in - Destination.
these headers are grouped so its easier to look up a vehicle.
these columns repeat 25x.
so far so good,
i have for each vehicle 4 inputs, so it will look like this:
------------(A)--|-(B)-|(C)|(D)|
(1)-------LICENSE|CHAUF|OUT| IN|
(2)INPUT 1
(3)INPUT 2
(4)INPUT 3
(5)INPUT 4
it now correctly marks when a date overlaps for the same vehicle,
(" =SOMPRODUCT(($G3=$G$3:$G$6))>1 "), as it only needs to look up in a small cluster.
but now i have the following issue, we have loads of personal chauffeurs.. (a file of 35 chauffeurs over 25 vehicles)
which need to be checked up in different (divided) colums.
i have prevented planning the same vehicle twice in the same timeframe.
but now i need it to lookup if the chauffeur is not planned twice! (not vehicle dependend!)
EG.
License is column A,E and I
Chauffeur is column B, F and J
Date out is column C, G and K
Date in is column D, H and L
My instinct is telling me this:
=SUMPRODUCT(($C2=$C$2:$C$5+$G$2:$G$5+$K$2:$K$5)*($B2=$B$2:$B$5+$F$2:$F$5+$J$2:$J$5))>1
could you please correct my code?
Robin,
Example with eight columns:
Conditional formatting formula in cell range A6:D12:
Conditional formatting formula in cell range G6:J12:
See attached file:
highlight-overlapping-dates-robin.xls
It would have been a lot easier if you had all records in 4 columns.
Example:
Thank you very Oscar, this formula is working ,
I am using it in range of few hundred and its giving the perfect result. I did adjust a bit to suit for my requirement
=SUMPRODUCT(($C6$C$6:$C$120)*($B6=$B$6:$B$120))>1
I have used the formula as below
=SUMPRODUCT(($C6$C$6:$C$120)*($B6=$B$6:$B$120))>1
Jahid,
I am happy you like it.
Hi Oscar,
Great website! Keep up the good work.
I have a question as to how to expand this to the next step. Per your initial example we can see that Jeff in Row 1 and Shaun in Row 4 overlap their work schedule. I would like to know if there were a way, say for instance in Range E6:E12, to display Jeff’s Row number next to Shaun’s name and visa – versa. I can see how this will easily display what record numbers overlap when there is only one overlap in the range. Can we differentiate the records when there are two overlaps? Let’s say Theodor in Row 6 also has an overlap with Thomas in Row 2.
Now the data becomes confusing because we have to determine, from the 4 grayed rows, who overlaps with whom.
Can a formula return the Row value of the “matching” overlap record?
In Jeff’s E6 cell it would indicate “Row 4” as to the matching record, and the reverse for Shaun. Shaun’s E9 cell would indicate “Row 1” as the matching record.
And at the same time Theodor’s E11 cell would reflect “Row 2” for Thomas’s record and Thomas’ E7 cell would show “Row 6” for Theodor.
I haven’t even touched on the tougher one, such as what happens when John in Row 7 has an end date of 2010-01-07, thus overlapping with both Jeff and Shaun!
One step at a time. :)
Thanks
cwrbelis,
Great question!
Read this article:
Identify overlapping records
Hi Oscar, I have what I think is a simple scenario which I thought I could solve using your overlapping date range example but I am not getting the results I expect.
At its simplest form, the cells are:
A1 04/01/2004 B1 08/01/2008
A2 11/01/2003 B2 15/01/2012
in D1 I have {=$B1>=$A2:$B2}
I want a TRUE/FALSE result should the B1 date fall between the date range for A2 (start date) to B2 (end date)
Martin,
Formula in D1:
=(B1>=A2)*(B1<=B2)
Thank you Oscar! Can you elaborate on taking the same dates as whole ranges to compare? So if I wanted a TRUE/FALSE for when the date period starting from A1 and ending at B1 overlaps the start date in A2 and the end date in B2?
Martin,
Can you elaborate on taking the same dates as whole ranges to compare?
That is what the formula in this post does. It compare cell C6 with cell range D6:D12 and cell D6 with cell range C6:C12.
=SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1
$C6<=$D$6:$D$12 returns an array and $D6>=$C$6:$C$12 returns an array. By multiplying both arrays you check if both values on each row is TRUE. If more than 1 value returns true the whole function returns true. That means there is at least one overlapping date range.
Thanks again Oscar. I apologise for not being clear, in my scenario I am attempting to find out dates that overlap in these cell ranges and to indicate the overlap. I have tried using conditional formatting and formula =SUMPRODUCT(($G9=$G$9:$G$29))>1 but I am still not having much success at all.
In the sample below, below each date are hidden calculations / formula that I want to exclude from the overlapping check, I only wish the cells below to have the overlapping date check applied. I want to know if any DATE START and DATE END overlap, ideally through conditional formatting to highlight the overlapping date ranges.
CELL START CELL DATE END
G9 01/01/2001 P9 01/01/2002
G14 01/02/2001 P14 01/04/2002
G19 01/03/2003 P19 01/04/2004
G24 01/04/2004 P24 01/06/2005
G29 01/09/2005 P29 01/01/2010
Am I failing at this because of the data held underneath each date range, as specifying G9:G29 for example will include cells that I want to exclude. I've tried =SUMPRODUCT(($G9=$G$9+$G$14+$G$19+$G$24+$G$29)>1 but with no success either. Perhaps I should be looking at another formula rather than SUMPRODUCT?
For some reason my previous post was incomplete. The formula's should read:
=SUMPRODUCT(($G9=$G$9:$G$29))>1
and
=SUMPRODUCT(($G9=$G$9+$G$14+$G$19+$G$24+$G$29)>1
Martin,
I hope this helps:
Get the Excel file
overlapping-date-ranges.xlsx
Hi Oscar
I manage our family's shared holiday cottage, and need something almost like your example, but with one difference:
People can arrive on the same day the previous people are departing, so that doesn't count as an overlap.
How do I change the formula?
Thanks a lot!
Anton,
interesting question.
Try this:
It works perfectly!
Thanks a LOT Oscar, your prompt reply is appreciated!
Hi,
In the example with eight columns which is what I am trying to do, is it possible not to have it look at it's own columns but just across?
So the range C&D would only look at the range I&J and report any overlapping ranges between those only?
TIA
Erik
Erik
Conditional formatting formula, cell range A6:D12:
=SUMPRODUCT(($C6<=$J$6:$J$12)*($D6>=$I$6:$I$12)*($B6=$H$6:$H$12))>0
Conditional formatting formula, cell range G6:J12:
=SUMPRODUCT(($I6<=$D$6:$D$12)*($J6>=$C$6:$C$12)*($H6=$B$6:$B$12))>0
There are overlapping date ranges in the first cell range but not highlighted, row 8 and 12.
The same thing with the second cell range, row 8 and 12.
Only row 8 in the first range is highlighted because it overlaps row 12 in the second range.
Hello Oscar, thanks a lot for this helpful website!
I am trying to apply the formula to a wider range than in your illustration. So instead of range "A6:D12", I am trying to apply it to the range "A6:D500" for example, where "A13:D500" are blank rows.
What happens is that all the blank rows get highlighted. Could you please help me with that problem?
Much appreciated