Identifying Overlapping Time Entries

I maintain a spreadsheet that tracks a technician, the invoice assigned to them, the date the invoice was opened and the technician's date/time on/off the job.  I have to capture any entry where the technicians date/time overlap. I have been doing this manually and it is becoming very tedious. Is there a way to automate the process? I have attached a small sample of my spreadsheet. Thank you in advance.
EE_TIME_OVRLAP.xlsx
castlerjSenior AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Here is a formula that you can use to determine whether there is an overlap. AutoFilter for the TRUE results, and those are your overlapping time entries.
=SUMPRODUCT((H$2:H$1000=H2)*((D$2:D$1000+E$2:E$1000)<(F2+G2))*((F$2:F$1000+G$2:$G$1000)>(D2+E2)))>1

In the above formula, the reference to row 1000 is arbitrary and should exceed any expected amount of data. Don't use the entire column, however, because it will take some time to perform all those tests!

Note that if you added the date and time columns (D & E and F & G) in some auxiliary columns, you could use a much simpler COUNTIFS formula. COUNTIFS only looks at the rows that contain data, so it is OK to use entire columns. See the ALT worksheet.
=COUNTIFS(H:H,H2,I:I,"<" & J2,J:J,">" & I2)>1
EE_TIME_OVRLAP_Q28736153.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
castlerjSenior AnalystAuthor Commented:
Thank you; I thought it was going to be complicated with comparing the technician and the specfic date.
byundtMechanical EngineerCommented:
When writing the SUMPRODUCT, I considered the possibility that a technician might work past midnight some day. That's why I had to add the date and time columns.

If you have strictly daytime operations, then you wouldn't need to test column F and could use a COUNTIFS like:
=COUNTIFS(H:H,H2,D:D,D2,E:E,"<" & G2,G:G,">" & E2)>1
castlerjSenior AnalystAuthor Commented:
Okay, I think I understand...one more question, why is the SUMPRODUCT limted to column length
byundtMechanical EngineerCommented:
The SUMPRODUCT test will be performed for every row that you ask. COUNTIFS, however, will only test rows that actually contain data. So COUNTIFS will be substantially faster than SUMPRODUCT if your worksheet has 1,048,576 rows--which a .xlsx file does.

By restricting the number of rows being tested in the SUMPRODUCT, the hit on speed is minimized to the point of being unnoticeable.

That said, the real question is whether you have any midnight warriors working at your firm. If you do, you need the SUMPRODUCT or the auxiliary columns. If not, you can go with the COUNTIFS I suggested in my previous Comment.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.