Excel Challenge #1 – Make Nuts without Going Nuts

Posted on March 9th, 2011 in Excel Challenges , Learn Excel - 61 comments

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:

  1. Display Ok  if the scheduling dates for that row do not overlap with remaining scheduling dates for that machine
  2. Display Not Ok otherwise.

Excel Challenge #1 - Find overlaps in machine scheduling dates

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

  1. Helper columns are ok
  2. Avoid VBA if possible
  3. 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 book
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:

Go ahead and post your answers. We are waiting.

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

61 Responses to “Excel Challenge #1 – Make Nuts without Going Nuts”

  1. Will Clark says:

    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")

  2. Alon Nisser says:

    =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")

  3. Busymanjohn says:

    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.

  4. Luke M says:

    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")

  5. Will Clark says:

    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")

  6. Will Clark says:

    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")

    • Omar says:

      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

  7. Luke M says:

    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. =/

  8. Busymanjohn says:

    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"),"").

  9. GilesS says:

    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")

  10. Elias says:

    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

  11. Tristan says:

    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

  12. Mark says:

    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"

  13. =IF(SUMPRODUCT(($C$4:$C$10=C4)*((($D$4:$D$10=D4))+(($E$4:$E$10>=E4)*($D$4:$D$102,"Not OK","OK")

  14. Mark says:

    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"

  15. GilesS says:

    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”)

  16. GilesS says:

    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}

  17. GilesS says:

    OK I give up - will email excel with correct formula

  18. Istiyak says:

    Nice attempt chandoo & team.

  19. Adilson Puro says:

    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.

  20. ben says:

    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")

  21. ben says:

    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")

  22. Jialin Li says:

    =IF(SUMPRODUCT( --([ID]B4), --([Machines]=C4), --([Start]=D4)) + SUMPRODUCT( --([ID]B4), --([Machines]=C4), --([Start]=E4)),"No","Yes")

  23. Jialin Li says:

    =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")

  24. Elias says:

    @ Ben
    Did you check all the previous formulas? Yours is not the first one using COUNTIFS.

    Regards

  25. Rohit1409 says:

    Hi Chandoo,

    Got this one, I have emailed you the file.

    Regards
    Rohit1409

  26. Prem Beejan says:

    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

  27. Pedro Wave says:

    Writing into F4.
    =IF(SUMPRODUCT(--(C4=[Machines]),--(E4>=[Start]),--(D4<=[End]))=1,"OK","Not OK")

  28. Prem Beejan says:

    @ Pedro
    Thanks for the formula
    Prem

  29. Stef@n says:

    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

  30. Durheim says:

    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.

  31. Durheim says:

    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")

  32. Pablo says:

    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.

  33. Pedro Wave says:

    @ Prem, you're welcome!

    Write a formula between code tags if it have signs: >, >=, <, <=

    http://htmldog.com/reference/htmltags/code/

  34. Elias says:


    @ Pedro I like the formula and the tip to avoid the signs issue
    Thanks
    </code.

  35. Rick Rothstein (MVP - Excel) says:

    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.

  36. Michael Pennington says:

    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.

  37. [...] 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? [...]

  38. Cecilia says:

    =IF(SUMPRODUCT(--($C5:$C$9=$C4),($D4>=($D5:$D$9))*($E4<=($E5:$E$9))+($D4=($D5:$D$9)))>0,"Not OK","OK")

  39. xlfan says:

    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

  40. Jerome Ware says:

    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")

  41. chrisham says:

    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

  42. Laksiri says:

    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")

  43. Andy Holaday says:

    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")

  44. Udayakumar.K says:

    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.

  45. Hui... says:

    @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

  46. udayakumar.K says:

    Hui,
    Many thanks. it is working fine. Sorry for late reply.
    it was very much helpful.

  47. cristian says:

    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

  48. Chandoo says:

    @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.

    • Omar says:

      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 !

  49. Balasubramaniam K says:

    =IF(SUMPRODUCT((C$2:C$7=C2)*(B$2:B$7=B2))>1,"Not OK","OK")

  50. Balasubramaniam K says:

    =IF(SUMPRODUCT((C$2:C$7 lessthanequalto D2)*(D$2:D$7 greaterthanequalto C2)*(B$2:B$7=B2))>1,"Not OK","OK")

  51. Imran says:

    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")

  52. Elnur says:

    =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 

  53. Omar says:

    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")

Leave a Reply