Hi All, I'm looking for an excel formula that calculates the amount of time which overlaps different transactions for the same technician. The logic is that the technician should not be able to perform more than one task (i.e. transaction) during the same period of time. The file has labor transactions for around 80 technicians, with a workorder number, start date, start time, end date, end time, and transaction ID. There may be multiple transactions for the same workorder for the same technician; only the transaction ID is unique. The overlapping transaction could be before and during or during and after, but I only need the period of time which overlaps with another transaction for the same technician.
I was able to apply a SUMPRODUCT formula to identify where there is an occurrence of overlap for a technician, but it must be applied to each technician separately. If there is a way to apply it across technicians that would be great too. The formula used to identify an overlap for each technician is =SUMPRODUCT(((E2+F2<=$C$2:$C$21+$D$2:$D$21)+(C2+D2>=$E$2:$E$21+$F$2:$F$21)=0))<>1, where the columns of data are as follows:
A B C D E F
Tech Workorder Start Date Start time Finish Date Finish Time
Thank you in advance for your help!
I was able to apply a SUMPRODUCT formula to identify where there is an occurrence of overlap for a technician, but it must be applied to each technician separately. If there is a way to apply it across technicians that would be great too. The formula used to identify an overlap for each technician is =SUMPRODUCT(((E2+F2<=$C$2:$C$21+$D$2:$D$21)+(C2+D2>=$E$2:$E$21+$F$2:$F$21)=0))<>1, where the columns of data are as follows:
A B C D E F
Tech Workorder Start Date Start time Finish Date Finish Time
Thank you in advance for your help!
Attachments

10.9 KB Views: 29