Find Duplicates between Start and Stop Time

Kelly Moore

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.

Kelly

Paul Newcome

Time calculation would be a GREAT feature for Smartsheet to have. Feel free to Submit a Product Enhancement Request when you have a moment.

In the meantime... We are going to need to leverage some helper columns to convert the times into numerical values.

First we will need to standardize the formatting by removing those colons:

=SUBSTITUTE([End Time]@row, ":", "")

=SUBSTITUTE([Start Time]@row, ":", "")

Next we convert them to a numerical value

=VALUE(SUBSTITUTE([End Time]@row, ":", ""))

=VALUE(SUBSTITUTE([Start Time]@row, ":", ""))

Drop those into two helper columns [End Help] and [Start Help]

Now that we have that, the basic idea for checking for overlap would be to look for rows where the [Start Time] is less than the [End Time]@row and the [End Time] is greater than the [Start Time]@row.

=COUNTIFS([Start Help]:[Start Help], @cell <= [End Help]@row, [End Help]:[End Help], @cell >= [Start Help]@row)

Then we add in your other criteria for being the same date and Employee ID.

=COUNTIFS([Start Help]:[Start Help], @cell <= [End Help]@row, [End Help]:[End Help], @cell >= [Start Help]@row, [Employee ID]:[Employee ID], [Employee ID]@row, Date:Date, Date@row)

And that should work for you.

Paul Newcome

I can't remember all of the details behind it, but I have seen time zones come into play here. If I am not mistaken I believe the displayed date is based off of your selected time zone, but the back-end is stored as some other default time zone.

Instead of the DATEONLY function, you are going to want to use something along the lines of this:

=DATE(VALUE("20" + MID(Modified@row, 7, 2)), VALUE(LEFT(Modified@row, 2)), VALUE(MID(Modified@row, 4, 2)))

Paul Newcome

The problem is with the checkbox formula. To check a box, the number 1 can be used. Your COUNTIFS is generating the number 1 for each row because each row is unique. Try using an IF statement with it that will say if the COUNTIFS is greater than 1, then check the box.

=IF(COUNTIFS(...........) > 1, 1)

EDIT: Missing the IF statement was my fault. I was using the COUNTIFS to generate how many rows were matching the current one. I didn't finish it out in my initial solution. My apologies.

Paul Newcome

Time calculation would be a GREAT feature for Smartsheet to have. Feel free to Submit a Product Enhancement Request when you have a moment.

In the meantime... We are going to need to leverage some helper columns to convert the times into numerical values.

First we will need to standardize the formatting by removing those colons:

=SUBSTITUTE([End Time]@row, ":", "")

=SUBSTITUTE([Start Time]@row, ":", "")

Next we convert them to a numerical value

=VALUE(SUBSTITUTE([End Time]@row, ":", ""))

=VALUE(SUBSTITUTE([Start Time]@row, ":", ""))

Drop those into two helper columns [End Help] and [Start Help]

Now that we have that, the basic idea for checking for overlap would be to look for rows where the [Start Time] is less than the [End Time]@row and the [End Time] is greater than the [Start Time]@row.

=COUNTIFS([Start Help]:[Start Help], @cell <= [End Help]@row, [End Help]:[End Help], @cell >= [Start Help]@row)

Then we add in your other criteria for being the same date and Employee ID.

=COUNTIFS([Start Help]:[Start Help], @cell <= [End Help]@row, [End Help]:[End Help], @cell >= [Start Help]@row, [Employee ID]:[Employee ID], [Employee ID]@row, Date:Date, Date@row)

And that should work for you.

Kelly Moore

Thanks so much Paul!

I'll start working on it. Although I showed the time as 24hr, the users input time as 12hr am/pm clock. I'm going to strip the start time from Created. Unfortunately there are too many data touches to utilize Modified as the stop time. I plan to convert the data to 24hr clock as this will give them cleaner functionality for a sub routine they run - I'm reviewing some of your older posts to see if you have slick way of doing this vs a a horrible nested if.

I really appreciate the time you took with the formulas and the explanations. You're one of the community rockstars.

Kelly

Paul Newcome

I have some notes specifically regarding pulling from Created and Modified columns. I know you aren't using the Modified, but you may be able to at least get a good start on the created.

HERE is a published link to my sheet using the Created and Modified column types.

When your users enter times with am/pm, is the am/pm in the same cell as the time, and are they using colons consistently?

Kelly Moore

Yes, time + am/pm are written together in a single cell. I'm not sure about the consistency of colons - I would assume inconsistent as currently this process only uses paper forms. Last week I requested scans of actual hand-written forms but with current work restrictions, the data is a bit more difficult to obtain.

Ultimately the data from many forms (this started out with only two) are inputted manually to an Access database. The Access database will probably have to continue because of linkages to a multitude of other processes.

Paul Newcome

How is the data getting into Smartsheet from the Access database?

Kelly Moore

It's the other way around. Smartsheet will replace the paper forms, thus eliminating the tremendous manual assimilation that is taking place. Smartsheet will be used to populate Access.

Paul Newcome

Ah. So what is the feasibility of using a form to input the data into Smartsheet?

If you are able to use a form, then you could use a series of dropdowns to standardize the entry of data so that formulas for calculations wouldn't have to be nearly as complex.

The form can be set to reload after each submission, so data entry would actually go relatively quickly in the event someone needs to add multiple rows.

Kelly Moore

I welcome input. I have a form for starting the process. There is some basic data that I believe is known at that point. (waiting on the completed forms). My challenge is the additional data that is being collected throughout this process. It was easy with a paper form in a clipboard, to 'update' additional data, per employee id, for a group of people all day long. I'm not clear if the additional data is updated in one sitting, so an update request would suffice, or if they trickle in the information throughout the day. This might be too long a discussion for online.

Kelly Moore

Does the DATEONLY function change the timezone of the datestamp? It changed the date. I haven't noticed it doing this before.

Paul Newcome

I can't remember all of the details behind it, but I have seen time zones come into play here. If I am not mistaken I believe the displayed date is based off of your selected time zone, but the back-end is stored as some other default time zone.

Instead of the DATEONLY function, you are going to want to use something along the lines of this:

=DATE(VALUE("20" + MID(Modified@row, 7, 2)), VALUE(LEFT(Modified@row, 2)), VALUE(MID(Modified@row, 4, 2)))

Kelly Moore

I managed around the wrong date using this.

=IF(LEN(Modified@row) = 17, RIGHT(Modified@row, 8), RIGHT(Modified@row, 7)). It looks at the datestamp and counts characters. If the time is a double-digit number, like 10, etc, the length will be 17 characters. If true, it extracts 8 characters from the right. Otherwise it extracts 7 characters.

Paul Newcome

Yes. That would essentially "remove" the date and give you the time.

Kelly Moore

I thought the COUNTIFS duplicate formula worked, but it isn't working for me. In this table there are no duplicates however the checkbox field [Duplicates Detected] marked each row as duplicate (which triggered my conditional formatting). Start Time is hand entered in 12hr format to a form. AM/PM is selected from dropdown. In helper columns I strip Date and End Time , respectively, out of Created field. I converted time into decimal format (using Paul's technique Here). None of my start and end times cross midnight. I can't find my error.

Paul Newcome

The problem is with the checkbox formula. To check a box, the number 1 can be used. Your COUNTIFS is generating the number 1 for each row because each row is unique. Try using an IF statement with it that will say if the COUNTIFS is greater than 1, then check the box.

=IF(COUNTIFS(...........) > 1, 1)

EDIT: Missing the IF statement was my fault. I was using the COUNTIFS to generate how many rows were matching the current one. I didn't finish it out in my initial solution. My apologies.

Kelly Moore

Thanks again Paul. It made me laugh (with a groan) that I missed the IF statement. It's just one of those days.