Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Need suggestion for duplicates

Bill Brandt
Bill Brandt ✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

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?





  • Bill Brandt
    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
    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?

This discussion has been closed.