preventing overlapping date ranges

[xlfan]

Hi,

I am new to the forum! my friends talk a lot about this and glad to be in the forum using Office 2007.


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 alloted be changed.


Machines Start date End date

Machine 1 1/25/2011 2/7/2011

Machine 2 1/2/2011 1/25/2011

Machine 3 1/2/2011 1/25/2011

Machine 1 2/19/2011 3/22/2011

Machine 1 1/27/2011 2/11/2011


I use the Table feature of Excel 2007 for this data

 

[slaya_cz]

Hi xlfan


check this, I hope that it will help you - each time, when there is start date lower than last end date for particular machine it will display notice information

http://www.2shared.com/file/f2WdEfFx/Machines.html


Kind regards

Pavel

 

[xlfan]

Thank q Pavel!

Great example


but, I can book a machine even next year first and later come to the present year??


Like to have, no overlapping of the dates in the range on a machine.


Can you please help me with data validation or VBA

 

[oldchippy]

Take a look here


http://chandoo.org/wp/2010/06/01/date-overlap-formulas/

 

[xlfan]

Dear oldchippy!


Its explaining the overlapping system but not helping me


Thank q.

 

[Hui]

xlfan

you could always put an area over to the side of your data where you shade cells using conditional formatting, giving you a calendar/gannt chart effect

It would be simple to check if tasks overlapped

 

[keymaster]

@Xlfan.. I liked your problem very much. I have posted about it on chandoo.org too (you should see it in next 9 hours).


Since I converted this to a challenge, I did not want to publicly disclose my solution. So I will be emailing you a file that you can use to solve this. It seems to work alright for me.

 

[Rick Rothstein]

For all who are reading this thread...


xlfan posted this same question over in The Code Cage forum where I responded with a VB coded solution that xlfan indicated he was going to use. I thought it would be useful to post a link to that solution for those who might be interested. There are 3 web pages of responses which start here...


http://www.thecodecage.com/forumz/members-excel-vba-programming/208515-overlapping-date-ranges.html


My coded solution is located in Message #3.