We have a new series on chandoo.org. – Excel Challenges. From now, every 1-2 months, I will post an interesting Excel Challenge. These are tricky problems for which elegant solutions should be found. To keep the challenges exciting, we will have a small prize for a winner (if more people answer correctly, we pick one randomly)
Excel Challenge #1 – Find Overlaps in Machine Schedule Dates
The inspiration for this challenge came from our forums. This is a question asked by xlfan (I hope that is not a real name 😉 )
My question—
I work in a manufacturing company which makes different sizes of Nuts on many machines. One machine can do only one size of Nuts for a particular period and can be molded for another variant.I manage an excel sheet defining the date ranges of the machine utility for a particular Nut, this allows us to mark our next point for another size.The problem is.. after fixing dates for a process on a machine, I many time loose track of the previous marked dates and issue new dates for the same machine that is usually overlapping and lately found out.
Please help me with a solution ~~~ which does not allow overlapping of dates on a particular machine unless the previous dates allotted be changed.
I use the Table feature of Excel 2007 for this data
Problem Statement:
Download this Excel file. It has an Excel Table, with the following data (snapshot below). You need to write formulas to display Ok or Not Ok in the adjacent column based on the following criteria:
- Display Ok if the scheduling dates for that row do not overlap with remaining scheduling dates for that machine
- Display Not Ok otherwise.
How to post your answers?
- Post your formulas a comment
- Save the file and upload it to skydrive. Then paste the link to that file thru comments.
- Email me your file with solution at chandoo.d @ gmail.com. Please use the subject EC1 – solution to make sure that I notice your email.
If you are reading this in e-mail or RSS Reader, click here to post comments.
Other Rules
- Helper columns are ok
- Avoid VBA if possible
- Your solution should work in Excel 2007 or above.
What is in for me?
Glory! lots of it. But we know glory cannot give glee. So I am going to give a gift to one lucky winner.
One lucky winner gets:
Business Dashboards: A Visual Catalog for Design and Deployment
Last Date for Submission:
The contest part of this challenge ends on 18th March. That is we will pick the winner of book on that date. But you can continue to send solutions thru comments forever.
Some nuts to feed your brain:
- Original question by xlfan and follow-up discussion
- Finding date overlaps using Excel formulas
- Doing range lookups using Excel
- Examples of Excel Array Formulas
Go ahead and post your answers. We are waiting.
64 Responses to “Excel Challenge #1 – Make Nuts without Going Nuts”
A first attempt. Seems to work with the current data set. In cell F4 (then copy down):
=IF(SUMPRODUCT(($C$4:$C$9=C4) * ($D$4:$D$9=D4))-1,"NOT OK","OK")
=IF(SUMPRODUCT((Table1[[#This Row],[Start]]>[Start])*([Machines]=Table1[[#This Row],[Machines]])*((Table1[[#This Row],[Start]]>=[Start])*(Table1[[#This Row],[Start]]<=[End])+(Table1[[#This Row],[Start]]=[Start])))=0,"ok","not ok")
What about sorting the data first ,,, sort the data by Machine number then use an IF statement =IF(C4=C3,IF(E4>D5,"Not OK","OK"),""). This easy use of an IF statement returns OK if the dates do not overlap and Not OK if the dates do overlap and also checks to see if the machine numbers are the same and if they are not the same number then it leaves the space blank as it is the start of the next machines task.
To clarify on criteria, I'm assuming you don't want a start date equal to an end date (although, if going down to the hour, this might be possible?), thus, Machine 2 is "Not Ok"?
Formula:
=IF(SUMPRODUCT(--($B$4:$B$9=B4),--($C$4:$C$9=C4))=1,"Ok","Not Ok")
Let me try that again, the previous comment lost half my code:
=IF(SUMPRODUCT(($C$4:$C$12=C4)*($D$4:$D$12=D4))-1,"NOT OK","OK")
It did it again! Problem with using greater-than and less-than. Attempt 3.
=IF(SUMPRODUCT(($C$4:$C$12=C4)*($D$4:$D$12<=E4)*($E$4:$E$12>=D4))-1,"NOT OK","OK")
This formula is a great idea, but it takes only 1 case of overlap, that is when the start dates and end dates of machines are sorted in ascending order. also this formula compares the machine schedule with itself ! which means it is always not ok !!
but really the idea is great, a little work on it and it might work
Similar to Will, formula got chopped. Should be:
=IF(SUMPRODUCT(($B$4:$B$9=B4)*($C$4:$C$9 [less than or equal to] D4)*(D4:D9 [greater than or equal to] C4))=1,"Ok","Not Ok")
replace bracketed words with correct symbols. =/
Follow on to Luke M post ,,, if the Start Date = End Date, then my formula would be more like =IF(C4=C3,IF(E4>=D5,"Not OK","OK"),"").
Array Formula in G4= {=SUM(IF(C4=$C$4:$C$9,IF(D4=$D$4:$D$9,1,0),0),0))-1}
Formula in column F = if(g4>0,"Not Ok","Ok")
Maybe
=IF(COUNTIFS($B$4:$B$9,""&B4,$C$4:$C$9,C4,$D$4:$D$9,"GE"&D4,$D$4:$D$9,"LE"&D4)+COUNTIFS($B$4:$B$9,""&B4,$C$4:$C$9,C4,$D$4:$D$9,"GE"&E4,$D$4:$D$9,"LE"&E4),"Not Ok","Ok")
or
=IF(SUMPRODUCT( ($B$4:$B$9B4) * ($C$4:$C$9=C4)* ( ( ($D$4:$D$9"GE"D4) * ($D$4:$D$9"LE"D4) ) + ( ($D$4:$D$9"GE"E4) * ($D$4:$D$9"LE"E4) ) ) ),"Not Ok","Ok")
Replace GE for >= and LE for <=
Regards
Hi,
I assumed that an end date and a start date can not be equal, and there is data validation such that the end date must be greater than (or equal too) the start date.
Here are the formulas I have:
=IF(SUMPRODUCT(([Machines]=Table1[[#This Row],[Machines]])*(1-(Table1[[#This Row],[Start]]>[End]))*(1-(Table1[[#This Row],[End]][End])+(Table1[[#This Row],[End]]<[Start]))))-1,"Not OK","OK")
thanks,
Tristan
Whoops. I submitted the one via email before reading the instructions fully. Oh well, this one gives the correct results:
=REPT("Not ",SUMPRODUCT(--(Table1[[#This Row],[Machines]]=[Machines]),--(Table1[[#This Row],[Start]]=[Start]))>1) & "OK"
=IF(SUMPRODUCT(($C$4:$C$10=C4)*((($D$4:$D$10=D4))+(($E$4:$E$10>=E4)*($D$4:$D$102,"Not OK","OK")
Apparently copied and pasted the wrong thing. Ignore the earlier post:
=REPT("Not ",SUMPRODUCT(--(Table1[[#This Row],[Machines]]=[Machines]),--(Table1[[#This Row],[Start]]=[Start]))>1) & "OK"
as with others - my formula seems to have not come through correctly... odd...
array formula in g4 should be: {=SUM(IF(C4=$C$4:$C$9,IF(D4=$D$4:$D$9,1,0),0),0))-1}
formula in f4 then = if(g4>0,”Not Ok”,”Ok”)
this is now infuriating - I copy the formula in and it looks fine - I submit it and it changes... third time to test...
{=SUM(IF(C4=$C$4:$C$9,IF(D4=$D$4:$D$9,1,0),0),0))-1}
OK I give up - will email excel with correct formula
Nice attempt chandoo & team.
Hi Chandoo, a few days ago I sent you a file, a vacation/task planner that could be use the same way, check overlaps between dates for tasks.
Let me know your opinion about it.
why don't people ever use countifs? It's made for problem like this:
= IF( COUNTIFS([End],">="&Table1[[#This Row],[Start]], [Start],"1, "Not OK", "OK")
Will try once more - the blog mangles and then doesn't let me delete my previous post.
This formula goes in column F:
= IF( COUNTIFS([End],">="&Table1[[#This Row],[Start]], [Start],"1, "Not OK", "OK")
=IF(SUMPRODUCT( --([ID]B4), --([Machines]=C4), --([Start]=D4)) + SUMPRODUCT( --([ID]B4), --([Machines]=C4), --([Start]=E4)),"No","Yes")
=IF(SUMPRODUCT( --([ID].ne.B4), --([Machines]=C4), --([Start].le.D4), --([End].ge.D4)) + SUMPRODUCT( --([ID].ne.B4), --([Machines]=C4), --([Start].le.E4), --([End].ge.E4)),"Not OK","No")
@ Ben
Did you check all the previous formulas? Yours is not the first one using COUNTIFS.
Regards
Hi Chandoo,
Got this one, I have emailed you the file.
Regards
Rohit1409
https://cid-cf1aa5436558f176.office.live.com/view.aspx/.Public/excel-challenge-1-machine-scheduling-dates.xlsx
Got it.
Hi All
This is an array formula technique. So remember to press Shift+Ctrl+Enter. In F5 (F4 need no formula as it is the first) enter the following
=IF(MAX(IF($C$4:C4=C5,1,0)*$E$4:E4)=0,"ok",IF(MAX(IF($C$4:C4=C5,1,0)*$E$4:E4)>=D5,"overlaps","ok"))
The logic is that it checks if machine exists in previous rows and creates an array with the end dates. Then the MAX formula is used to determine which date is the last scheduled date. This date is then compared with the current entry to determine "ok" or "overlap".
It seems to work
Any comment will be welcome
Prem
what if i have format of date in dd/mm/yyyy hh:mm
Sorry
Forgot the file which is at
http://cid-75f325168218cdd9.office.live.com/view.aspx/.Public/EC1-machine-scheduling-dates-Prem%5E_Beejan.xlsx
Prem
Writing into F4.
=IF(SUMPRODUCT(--(C4=[Machines]),--(E4>=[Start]),--(D4<=[End]))=1,"OK","Not OK")@ Pedro
Thanks for the formula
Prem
Hallo Chandoo,
i have emailed you the file !
The "German" formula is in F4
=WENN(SUMMENPRODUKT(($D$4:$D$9=D4)*($C$4:$C$9=C4))>1;"Not OK";"OK")
best regards
Stef@n
My methodology for each line was to count the number of entries in the table where:
- The ID is different
- The Machine is the same
- The Start date is less than or equal to the end date of the line being checked
- The End date is greater than or equal to the start date of the line being checked
=IF(
COUNTIFS(Table1[ID],">"&Table1[@ID],
Table1[Machines],Table1[@Machines],
Table1[Start],"="&Table1[@Start])=0,
"OK", "Not OK")
This should work in all cases (I'm assuming that you don't want start date of one to be identical to another; so in the table shown Machine 2 should be flagged for ID 2 and 5.
Hmmm...similar issues as other posters; my formula got messed up.
=IF(
COUNTIFS(Table1[ID],">"&Table1[@ID],
Table1[Machines],Table1[@Machines],
Table1[Start],"="&Table1[@Start])=0,
"OK", "Not OK")
First step: sort the list based on the Start date, second use this formula:
=IF(ISERROR(VLOOKUP(C4,C5:$E$9,2,FALSE)),"OK",IF(VLOOKUP(C4,C5:$E$9,2,FALSE)>E4,"OK","NOT OK"))
Also, I left the last cell empty as by default is OK, if new lines are added, then the formula should be copied down as needed.
Done!
Please check the file
http://cid-f8a04d01004ea883.office.live.com/browse.aspx/.Public?uc=1
@ Prem, you're welcome!
Write a formula between code tags if it have signs:
>, >=, <, <=http://htmldog.com/reference/htmltags/code/
@ Pedro I like the formula and the tip to avoid the signs issue
Thanks
</code.
I know you said to avoid VBA if possible, but that is my "thing", so I developed an interactive (event driven) VB solution which alerts the user that a bad date is bad and in what way it is bad. First off, I should mention that xlfan's table is not as simple as Chandoo posted... the Machine Name column is not the first column in the table, there are columns of information between the Machine name and the Start/End Dates and there are columns of information after the Start/End Dates as well.
Here is a link to the VB event code that I posted back to xlfan over in The Code Cage Forum where he posted the question (Message #12)...
http://www.thecodecage.com/forumz/members-excel-vba-programming/208515-overlapping-date-ranges-2.html#post743585
If you are interested in seeing the layout for the table, xlfan posted it as an attachment in Message #11 in the same thread the above link takes you to.
For those who want to try the code out but are not familiar with installing event code, I provided instructions in Message #3, again, in that same thread.
Formula
=IF( (1+SUMPRODUCT((Machines=$C4)*(AllDates>$D4))) - (1+SUMPRODUCT((Machines=$C4)*(AllDates>$E4))) =1,"OK","Problem")Couldn't get my Skydrive to work, claims they are having server issues, so I posted it to google docs, instead. File here:
https://docs.google.com/viewer?a=v&pid=explorer&chrome=true&srcid=0B9kgqSZwNoWJNTM4NjY5MDAtNzFhNS00OTQyLTk2ZjMtOTcxNzg3ODI4MDI3&hl=en
Basically, I worked under the assumption that the ranking of each date should be sequential for a given machine. So for Machine 1, it should look like below and for each range, the start date minus the end date should equal 1 or there is a problem. Not sure if there is anything wrong with my logic, but I think it works. Keep up the great work on the blog, it is awesome.
[...] the way, you question formed the basis for a contest on another forum, here is the link to it... Excel Challenge #1 - Find Overlaps in Machine Schedule Dates | Chandoo.org - Learn Microsoft Excel O... Reply & Quote Reply Did you find this post helpful? [...]
=IF(SUMPRODUCT(--($C5:$C$9=$C4),($D4>=($D5:$D$9))*($E4<=($E5:$E$9))+($D4=($D5:$D$9)))>0,"Not OK","OK")
hello friends!
Great try on my post just starting to look for logic!
But got more than that - Chandoo thank you fro introducing more friends.
I liked Rick Rothstein work in VBA, perfect model for my use - Thank you Rick
http://www.thecodecage.com/forumz/members-excel-vba-programming/208515-overlapping-date-ranges-2.html#post743585
Wow, so many different ways to solve the same problem! A bit longwinded, but here's my $0.02.
=IF(COUNTIFS([Machines],[@Machines],[Start],"
<="&[@Start], [End],">="&[Start])+COUNTIFS([Machines],[@Machines],[Start],"<="&[@End], [End],">="&[@End])>2,"Not OK","OK")Forgot the link:
http://cid-c4ff6cc4bad9065a.skydrive.live.com/redir.aspx?resid=C4FF6CC4BAD9065A!104
This as got to be the longest formula out there..........
Array entered.
=IF(COUNTIF($C$3:C3,C3)=1,"OK",IF(SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT("A"&D3&":A"&E3)),ROW(INDIRECT("A"&MIN(IF($C$2:C2=C3,$D$2:D2))&":A"&MAX(IF($C$2:C2=C3,$E$2:E2)))),0)))),"Overlapping","OK"))
http://tinyurl.com/5w5mob9
Step 1 — Sort data set by machine, start date and by end date. Because we are going to check overlap in each machine.
Step 2 — Wrote a formula with 2 IF statements. Outer IF will looks through the list for machine name/number chage and the inner IF will compare the start date of a particular row against end date of previous row.
Here is the formula in "F9" ;
=IF(Table1[[#This Row],[Machines]]=C8,IF(Table1[[#This Row],[Start]]>E8,"OK","NOT Ok"),"OK")
I came late to the game. Here is another version using pure structured references. SR's look verbose but they are quick to write and self documenting. I'm not familiar with the "@" syntax posted by others (and it doesn't work for me in E2007). This can be pasted anywhere in the table under the "Ok?" header. The only assumptions are the ID field never repeats and is nondecreasing in value, and end date never precedes start date on any row.
=IF(COUNTIFS([Machines],Table1[[#This Row],[Machines]],[ID],">"&Table1[[#This Row],[ID]],[Start],"="&Table1[[#This Row],[Start]])=0,"Ok","Not Ok")
This works too. Less wordy, less clear what's going on though:
=IF(COUNTIFS([Machines],[Machines],[ID],">"&[ID],[Start],"="&[Start])=0,"Ok","Not Ok")
Hi friends,
I need a formula for searching dates,
in B2 I am having starting date, C2 it is ending date. from A4 to A25 I am having project completed dates.. I need in formula which should check the date between B2 (starting date) & C2 (ending date), if the project date A4 lies between the two dates then the date should be same in B4, if that date is lesser or greater then the starting & ending date there should be blank in B4.
can any one help me in this regard, Thanks in advance.
@Udayakumar.K
Give the following a spin
.
=IF(AND(A4>=$B$2,A4<=$C$2),A4,"") . make sure those " are shift ' cause they don't look right
Hui,
Many thanks. it is working fine. Sorry for late reply.
it was very much helpful.
Array formula:
{=IF(SUM((B4=Machines)*((Start>=C4)*(Start=C4)*(End2;"Not OK";"OK")}
or
{=IF(SUM((B4=Machines)*((Start>D4)+(End<C4)))=COUNTIF(Machines;B4)-1;"OK";"Not OK")}
where "Machines", "Start" and "End" are defined names for the respective column data (you can even define them as dynamic ranges).
Of course, you can use SUMPRODUCT to avoid array formulas
@All.. thank you so much for such varied and interesting responses. I will be posting a summary of these techniques on the blog soon.
I am declaring Luke M as the winner of this challenge. Congratulations Luke.
You will hear from me about the gift soon.
The winning formula must be flexible and adaptable, this formula works only on the current scenario, just change some dates, and it will give wrong results !
=IF(SUMPRODUCT((C$2:C$7=C2)*(B$2:B$7=B2))>1,"Not OK","OK")
=IF(SUMPRODUCT((C$2:C$7 lessthanequalto D2)*(D$2:D$7 greaterthanequalto C2)*(B$2:B$7=B2))>1,"Not OK","OK")
[...] http://chandoo.org/wp/2011/03/09/ec1-machine-scheduling-in-excel/ [...]
Write this formula in cell F4 and the copy & paste it in the cells below i.e. Cell F5 to Cell F9.
=IF(SUMPRODUCT(($D4>=$D$4:$D$9)*($D4<=$E$4:$E$9))>1, "Not OK", "OK")
=IF(MAX(IF((C5=$C$4:C4)/1=1;$E$4:E4);1)>D5;"No-No";"OK")
Startong from second row apply this formula with CSE
Array Formula
=IF(MIN((D3-(($C$3:$C$8=$C3)*$E$3:$E$8))*(E3-(($C$3:$C$8=$C3)*$D$3:$D$8))*ABS((D3-(($C$3:$C$8=$C3)*$E$3:$E$8))+(E3-(($C$3:$C$8=$C3)*$D$3:$D$8))))<0,"Not Ok","Ok")
This formula is totally flexible, sorting doesn't matter, number of machines doesn't matter
Copy to F4
=IF($C4="","",IF(C3="Machines","ok",IF(OR(C4="",D4=""),"not ok",IF(SUMPRODUCT(((D3:D$4=D4)*(C3:C$4=C4))+((D3:D$4=E4)*(C3:C$4=C4))+((D3:D$4>=D4)*(E3:E$4<=E4)*(C3:C$4=C4))+((D3:D$4=D4)*(C3:C$4=C4)))=0,"ok","not ok"))))
=IF(
COUNTIFS(
[ID],""&[@ID],
[Machines],"="&[@Machines],
[Start],"="&[@Start])=0,
"OK","Not OK")