E-Mail 'Advanced Date Highlighting Techniques in Excel' To A Friend
Email a copy of 'Advanced Date Highlighting Techniques in Excel' to a friend
Loading ... Loading ...85 Responses to “Advanced Date Highlighting Techniques in Excel”
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
Thanks. It works perfectly.
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
Hi Oscar,
Please help me.
In due date i used this formula to track if the employee already exceed in her Inactive status.
NOTE: g1=DATE TODAY
=IF(E2=$G$2+5,"5 DAYS BEFORE DUE",IF(E2=$G$2,"EXPIRED",IF(E2<$G$1,"EXCEEDED", "--"))
If ever the employee Back to work from Inactive status. I would like to erase automatic the "EXCEEDED" in column Due date.
What formula can i used? Please help me
HRID LName Status Start End Due Date
57456 RODRIGUEZ INACTIVE 6-Jan-12 15-Jan-12
65153 MENDOZA INACTIVE 1-Sep-08 9-Apr-12
57456 RODRIGUEZ BACK TO WORK 15-Jan-12 5-Jan-12
57613 JOSE INACTIVE 16-Mar-10 8-Jun-11
Ana,
Get the Excel *.xlsx file
Ana.xlsx
Oscar,
Thank you so much for your help... This is great...
Thank you Mr.Genius
Hi Oscar,
If possible, if ever I can add Separated status in column C(Status). If ever she BACK TO WORK or SEPARATED the "EXCEEDED" in column Due date automatic erase.Please advise
Thank you
Oscar,
Please help me....What formula can i used?
Oscar,
Please help.....
Ana,
I don´t think I understand. Your first question:
If ever the employee Back to work from Inactive status. I would like to erase automatic the "EXCEEDED" in column Due date.
Your next question:
add Separated status in column C(Status). If ever she BACK TO WORK or SEPARATED the "EXCEEDED" in column Due date automatic erase.
Anyway I tried, get the Excel *.xlsx file:
https://www.get-digital-help.com/wp-content/uploads/2010/10/ANA1.xlsx
Oscar,
This is awesome formula Oscar. Thank you so much .
THANK YOU........
ah great formula Oscar! interesting...
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.......
Confused :( Both formulas looks like same. do i have to replace MAX part in second formula with MIN ?? . Also guide me how to use them, where should i insert the formula?
Thank you very much Oscar
srini
srinivas,
Confused Both formulas looks like same. do i have to replace MAX part in second formula with MIN ??
Yes, you are right! It is now corrected.
Also guide me how to use them, where should i insert the formula?
It is a conditional formatting formula.
How to apply the conditional formatting formula in excel 2007:
1. Select cell range B2:B159
2. Go to tab "Home"
3. Press with left mouse button on the "Conditional formatting" button
4. Press with left mouse button on "New rule..."
5. Press with left mouse button on "Use formula to determine which cells to format"
6. Type formula
7. Press with left mouse button on "Format..." button
8. Select a color.
9. Press with left mouse button on OK twice.
Repeat steps and use the second formula and preferbly another color.
Thank you very much Oscar. It is now clear :)
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.....
Hi Oscar,
I have difficulty finding out the overlapping of dates. see example:
EMP ID TYPE OF LEAVE START END
1234 Sick 13-Mar-2012 13-Mar-2012
1234 Sick 10-Mar-2012 18-Mar-2012
5678 Annual 11-Feb-2012 12-Feb-2012
5678 Annual 12-Feb-2012 15-Feb-2012
5678 Annual 09-Feb-2012 18-Feb-2012
I'm checking for over 4,000 records. PLEASE HELP!
THANK YOU SO MUCH.
Lucy,
Is this what you are looking for?
Lucy.xlsx
Yes is there any formula to highlight the overlapping dates?
Thanks!
Lucy,
the formula I provided highlights overlapping dates only if the EMP ID is the same.
This formula highlights records with overlapping dates.
SUMPRODUCT(($C2<=$D$2:$D$6)*($D2>=$C$2:$C$6))>1
Hey Oscar,
I am trying to do something similar but cannot figure it out. if you could help I would really appreciate it as I need to fix our database this week & remove duplicate
Column J = Ad Id #
Column f = Date
I am trying to highlight all of the cells where the AD ID# was entered for the same date. The AD ID #'s are all unique so my logic is that there should never be the same AD ID entered into the spreadsheet with the same date.
I have tried this formula, but no luck - =SUMPRODUCT(($J2=$J$2:$J$9263))>1
Thanks for your help!Let me
* This formula
=SUMPRODUCT(($J2=$J$2:$J$9263))>1
Hmm I am not sure why the whole formula is not showing up..
I will break it up
=SUMPRODUCT(($J2=$J$2:$J$9263))>1
Carter Mahoney,
Conditional formatting formula:
=SUMPRODUCT(($A2=$A$2:$A$7)*($B2=$B$2:$B$7))>1
Carter-Mahoney.xlsx
hey this formula works perfectly for each month but how do I make it do it by year and month? I have about 2600 lines I have to highlight each max and min value for each month of each year.
Pradeep,
Conditional formatting formula min value:
Conditional formatting formula max value:
Get the Excel *.xlsx file
Highlight-max-and-min-value-in-every-month.xlsx
I'd like to do this for a row instead of a column, but I'm stuck. (I replace 'A1' with '1:01'.) Can this be done?
Kate,
I have have added more content, I hope it will answer your question.
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.
PLEASE HOW TO CALCULATE OVERLAPING DAY IN NUMBER
FOR EX.
2/1/2012 5/1/2012
3/1/2012 4/1/2012 2 DAYS OVERLAPING
SAMAN,
read this post: Count overlapping days in a date range
i am trying to do a conditional formatting for a calendar row. (essentially, a gant chart)
i have a schedule table with event in 1st column, start date in 2nd column, end date in 3rd column, and a 2-criteria condition for the event in the 4th column.
how do i condition format the cell row beneath the calendar row so that they are within the start/end date and will be color coded brown or blue based on the 2-criteria? I already have a conditional formatting done for weekends and holidays for the calendar row itself, so hence the new row beneath.
bryan,
Can you provide example data and desired outcome?
Oscar,
Thank you for the reply. Further info provided:
I have a schedule table with 4 columns.
1. Column D has the event name
2. Column E has the Start date
3. Column F has the End date
4. Column G has a drop-down choice of "home" or "out of area"
I have a calendar going from I1:IV1. I have it setup for highlighting federal holidays and weekends for conditional formatting.
I have a row setup beneath the calendar from I3:IV3 that I'd like to conditionally format to reflect based on the start-end date ranges in the schedule table with color coding for "home" or "out of area".
Thank you.
bryan,
Read this: Highlight events overlapping federal holidays
[...] Bryan asks: [...]
Hi
Regarding your section on 'HIGHLIGHT RECORDS', how do I Conditional Format to highlight dates in range that are recognised, but then automatically highlight all cells in the row?
IE: Using your HIGHLIGHT RECORDS example:
Conditional Format (as per your formulae provided) to recognises dates between 10-01-2009 & 20-01-2009
Then automatically highlight COUNTRY & COMPANY once dates recognised by Conditional Formatting.
John-Paul
I don´t understand.
The "Highlight records" example highlights all cells (also country and company) in a row?
Can you explain in greater detail?
Hi,
I am making a Schedule of Project in Excel, Where i am taking 2 column as Start date and End date. While I need that whenever i enters the start date and end date , according to that rows color changes simultaneously..... While after End date column, Next all columns belongs to week wise column. so every cell color changes accordingly with respect to weekwise.
Please let me know how to set a format like this....
Thru which color changes automatically, whenever i enters dates.
Avin
Avin,
I am not following, can you please take a screenshot of your sheet and the desired outcome. Upload to postimage.org. Then add the picture link to your comment.
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
I've tried the above method but only two of my dates actually got highlighted. Is there and issue in Excel 2010?
I have dates in two particular cells and I need a row of dates to highlight a particular color if the dates fall on or between the particular dates. I think the formula for rows above should work but it is not.
Jen
Jen,
I've tried the above method but only two of my dates actually got highlighted. Is there and issue in Excel 2010?
No, it works in excel 2010.
Is there an issue on the attached file? The attached file works here (excel 2010).
Can you provide your Conditional Formatting formula?
Did you check the absolute and relative cell refs in your CF formula?
Here is a pdf of what I'm talking aobut. I've applied the conditional formatting, but the cells that are white (Y7 to AM7) *should* be green.
https://s26.postimg.org/p77hmqeuh/Page_1_Conditional_Formatting_Sample.jpg
Jen,
your CF formula should be:
=(Y7>=$I$7)*(Y7<=$J$7) not =(Y7<=$I$7)*(Y7>=$J$7)
Hi Oscar,
I hope this site is still active.
I'm looking for a formula that will identify/highlight duplicate names within every six week time period.
So if 'John Smith' appears twice within any six week period it would highlight his name.
I only have 2 columns, date and name.
Thanks!
Hi,
I used the above formula and it worked. However when I have some rows having blank dates, it applies the formatting also.
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.
It works well. Exactly what i needed.Thank you so much =D
Dear Oscar,
How can I do this for the repeating textual items in column A instead of dates? For example, I would like to determine min values for pears, apples, oranges, whereas the series looks like this:
Pear 5.5
Pear 1.2
Apple 3.2
Apple 5.5
Pear 1.2
Orange 6
Apple 3.2
Orange 5.5
Pear 1.2
Thank you in advance.
Best regards,
Goranka
Goranka
Great question.
Array formula in cell E1:
=MIN(IF(D1=$A$1:$A$9,$B$1:$B$9,""))
Array formula in cell E2:
=MIN(IF(D2=$A$1:$A$9,$B$1:$B$9,""))
Array formula in cell E3:
=MIN(IF(D3=$A$1:$A$9,$B$1:$B$9,""))
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
Hi,
would love to know whether there is a formula to find how many days, two dates range, are falling in a certain period.
e.g.: start date - 06/22/2017 end date - 06/28/2018
I would like to create a formula which returns the number of days, between above mentioned dates range, falling in calendar year 2017 and 2018.
can you help me?
Many thanks,
Dor
Dor Cohen,
You can use the MEDIAN function to count overlapping days:
https://www.get-digital-help.com/days-contained-in-a-range-that-overlap-another-range/
I have sheets that I scan data to from bar codes. I want to highlight the data by the day of the week it was scanned in. If it is Monday I want the cells to be one color, then the next day I want them to scan a different color. Once color for each day Mon-Sat
I know I can make cells highlight based on the weekday if the cell contains a date but that is not what I am trying to do. These cells will be blank until data is scanned into them. At that point I would like the highlight to change to a different color depending on the current weekday.
What forumula is required to record the highest value during live data in the Excel seat
I find this is a very helpful page for my research for time series analysis.
Hey guys can some one help me modify the overlaping formula so that it would only get TRUE depending on one more column duplicated values? Screenshot below:
https://i.postimg.cc/7YM6Hxdm/Capture.png
Tried to modify the formula myself but my changes seem to not have any effect on the outcome.
=SUMPRODUCT((C$2=T_LEAVE[END DATE])*(B$2=T_LEAVE[EMPLOYEE_NAME]))>1
Tadas,
There seems to be nothing wrong with your formula, however, the image shows cell G8 selected and the formula references cells on row 2. Why is it not referencing cells on the same row (row 8)?
Dear Oscar,
I hope that this message finds you in good health.
Could you kindly help me out with the formula to highlight "Tool" used on different "Resource" on same "Planned Start Date" & "Planned End Date".
Thank you.
Resource Tool Total Req Hrs (Including C/O) Planned Start Date Planned End Date
UHL-A 7A/1709 30.94 5/3/2022 6/3/2022
UHL-A 7A/1709 2.58 6/3/2022 6/3/2022
UHL-A 7A/1709 6.66 6/3/2022 7/3/2022
UHL-A 7A/1709 6.22 7/3/2022 8/3/2022
UHL-A 7A/1709 1.59 8/3/2022 8/3/2022
UHL-A 7A/1709 16.59 8/3/2022 8/3/2022
UHL-A 7A/1709 8.15 8/3/2022 9/3/2022
UHL-A 7A/1709 5.02 9/3/2022 9/3/2022
UHL-B 3F/1780 7.02 7/3/2022 7/3/2022
UHL-B 3F/1780 14.09 7/3/2022 8/3/2022
UHL-B 3F/1780 13.79 8/3/2022 9/3/2022
UHL-B 3F/1780 13.8 9/3/2022 9/3/2022
UHL-C 7A/1709 16.91 7/3/2022 8/3/2022
UHL-C 7A/1709 5.46 8/3/2022 8/3/2022
UHL-C 3F/1780 12.14 6/3/2022 7/3/2022
UHL-C 3F/1780 12.14 7/3/2022 7/3/2022
UHL-C 3F/1780 1.67 7/3/2022 7/3/2022
UHL-C 3F/1780 5.78 9/3/2022 9/3/2022
Dear Oscar,
I hope that this message finds you in good health.
Could you kindly help me out with the formula to highlight same "Tool" in two difference "Resource" within overlapping "Planned Start Date" & "Planned End Date".
Thank you.
Resource Tool Total Req Hrs (Including C/O) Planned Start Date Planned End Date
UHL-A 7A/1709 30.94 5/3/2022 6/3/2022
UHL-A 7A/1709 2.58 6/3/2022 6/3/2022
UHL-A 7A/1709 6.66 6/3/2022 7/3/2022
UHL-A 7A/1709 6.22 7/3/2022 8/3/2022
UHL-A 7A/1709 1.59 8/3/2022 8/3/2022
UHL-A 7A/1709 16.59 8/3/2022 8/3/2022
UHL-A 7A/1709 8.15 8/3/2022 9/3/2022
UHL-A 7A/1709 5.02 9/3/2022 9/3/2022
UHL-B 3F/1780 7.02 7/3/2022 7/3/2022
UHL-B 3F/1780 14.09 7/3/2022 8/3/2022
UHL-B 3F/1780 13.79 8/3/2022 9/3/2022
UHL-B 3F/1780 13.8 9/3/2022 9/3/2022
UHL-C 7A/1709 16.91 7/3/2022 8/3/2022
UHL-C 7A/1709 5.46 8/3/2022 8/3/2022
UHL-C 3F/1780 12.14 6/3/2022 7/3/2022
UHL-C 3F/1780 12.14 7/3/2022 7/3/2022
UHL-C 3F/1780 1.67 7/3/2022 7/3/2022
UHL-C 3F/1780 5.78 9/3/2022 9/3/2022