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
EE_TIME_OVRLAP.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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:
ASKER
Okay, I think I understand...one more question, why is the SUMPRODUCT limted to column length
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.
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.
ASKER