Thread: preventing overlapping date ranges

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

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.