preventing overlapping date ranges

**xlfan** · 03-01-2011, 02:52 PM

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

**NBVC** · 03-01-2011, 03:42 PM

If I understand correctly, you can apply Data Validation through the Data menu.

The only caveat is that data validation rules don't seem to get auto-applied in Tables as you insert new rows. So you will have to preselect the range starting at B2 and ending in column C at a row you think the data should never reach.

Then go to Data and select Data validation from the Data Validation drop down.

Then select Custom from the Allow menu and enter formula:

=COUNTIFS($A$2:$A2,$A3,$B$2:$B2,"<="&B3,$C$2:$C2,">="&B3)=0

The validation won't apply to already completed cells, but if you insert a new row and begin entering data, you should get an error if the data overlaps another already entered date range for that machine. You will get a popup error message (which you can customize through Data validation windows).

**xlfan** · 03-01-2011, 10:03 PM

Originally Posted by NBVC

If I understand correctly, you can apply Data Validation through the Data menu.

The only caveat is that data validation rules don't seem to get auto-applied in Tables as you insert new rows. So you will have to preselect the range starting at B2 and ending in column C at a row you think the data should never reach.

Then go to Data and select Data validation from the Data Validation drop down.

Then select Custom from the Allow menu and enter formula:

=COUNTIFS($A$2:$A2,$A3,$B$2:$B2,"<="&B3,$C$2:$C2,">="&B3)=0

The validation won't apply to already completed cells, but if you insert a new row and begin entering data, you should get an error if the data overlaps another already entered date range for that machine. You will get a popup error message (which you can customize through Data validation windows).

Thank you very much!
Can you please help me with an example sheet.??

Also, I should not be able to enter any dates on that machine when there is overlapping before or after unless I select for a new dates or change the previous entry.

Can a VBA code be formulated for this! this work will be a treasure to me.

Thanks in advance.