Find Duplicates between Start and Stop Time
Hello Smart (sheet) Friends
To minimize data entry errors in a timesheet application where data is entered by a supervisor for a group of individuals, I would like to identify duplicates where an employee is assigned to a task (same or different) within the same time period the employee is assigned to another task. (hey smartsheet, we would love a time function!) All tasks stop and stop within the same 24 day.
If I was only looking for exact matches I believe the formula would be =IF(COUNTIFS([EMPLOYEE ID]:[EMPLOYEE ID], [EMPLOYEE ID]1, DATE:DATE, DATE1, [START TIME]:[START TIME], [START TIME]1, [END TIME]:[END TIME],[END TIME]1) = 1, 0, 1) . However, since I'm looking for matches within a time window, I'm unsure of the formula. I'm open to any suggestions and I can add any helper columns needed.
Thanks in advance for your time.
Help Article Resources
Check out the Formula Handbook template!