Excel Challenge #1 – Make Nuts without Going Nuts

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.

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