Need suggestion for duplicates

Bill Brandt

I have a sheet that allows deliveries to be entered. They have a date, location, start time and end time. I have restricted the times to whole hrs 5AM to 10PM. I would like to know if some enters a delivery at the same place on the same date with a time that overlaps another. Any suggestions on how this might be accomplished?

 

Thanks

 

Bill Brandt

I tried the sort approach with this;

=IF([Delivery Date]19 = [Delivery Date]18, IF([Load Location]19 = [Load Location]18, IF([Delivery Time Start]19 < [Delivery Time End]18, "T", ""), ""), "")

 

It has several issues. One, if I change the start time of the now flagged item to be greater than the end time it does not seem to evaluate again and maintains the conflict tag. Even after a refresh. Two, it is unclear what will happen as new items are added by form or the sheet gets resorted on different criteria. It seems that once evaluated it never changes? We really need a persistant sort that can be forced on all viewers. Having to have each user sort multiple criteria every time they view the sheet is not really productive.

Bill Brandt

Another issue: Since there is no time format I am using a dropdown like this;

5:00 AM, 6:00 AM, 7:00 AM and so forth. it seems that when I try to ask if 10:00 AM < 8:00 AM I don't get the answer I expect (no) but on another set of rows I get yes for 8:00 AM is < 9:00 AM?