Calculate Overlapping Time in Excel

Hillary

New Member
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

Attachments

• 10.9 KB Views: 31

Thank you, Luke!

colton.williams

New Member
So, trying to modify this to calculate the overlapping time of 2 technicians. Any heed of advice how to make it see \$K\$2 aswell as \$K\$3? Then go forward in it's calculations?

Luke M

Excel Ninja
Hi colton,
Could you provide a sample of your data layout, and what type of output you are looking for?

colton.williams

New Member
Unfortunately I don't have access to Excel at my location... BUT! We can work with the example you posted from MREXCEL:
http://www.mrexcel.com/forum/excel-...late-amount-overlapping-time.html#post3503049

So instead of calculating the overlapping time of just technician 14 or just the overlapping times of technician 15. I'm trying to figure out how to see how much Technician 14 overlaps Technician 15, accounting for the 4 different time overlap scenarios:

(A<C)(B<D)
A----------B
C-----------D

(A>C)(B>D)
A-----------B
C--------- D

(A<C)(B>D)
A-------------B
....C------D

(A>C)(B<D)
....A------B
C-------------D

And of course another modification to the MREXCEL table there would be that the times of Technician 14 and 15 actually do overlap.

Last edited:

Luke M

Excel Ninja
W/o XL, not sure how you're going to check this but basic formula would be:
=IF(OR(A>D,B<C),"No overlap",MIN(B,D)-MAX(A,C))

colton.williams

New Member
That did the job perfectly. Thankyou so much!

trizahler

New Member
Hello, thank you in advance for any replies.

I am trying to piggyback off an older thread here to develop a spreadsheet formula tool to identify timecard overlaps.

https://www.mrexcel.com/forum/excel-questions/709968-calculate-amount-overlapping-time.html#post4932454

I am trying to compare shifts worked by home medical workers (PCAs) at different agencies attempting to find overlaps with the formula posted in this forum topic.

Column N ties Columns B, C,D, E together, but I do not know how to expand the formula in Column N to tie Columns H,I,J,K together as well.

The workers are attempting to work at two different organizations at the same time and be paid for it. I am attempting to identify the fraudulent times worked by identifying timecard overlaps.

Also Column Q is supposed to calculate a total number hours of overlap, but I cannot seem to get that formula to work.

Any advice or guidance would be most appreciated.

NARAYANK991

Excel Ninja
Hi ,

I am attaching a file which can only help partially ; all I have done is extend your formula to cover the various tests for overlapping :

1. You already had a formula in column N to test for overlapping within one organization A , by comparing the start and finish times in columns B , C , D and E.

2. I extended this to do a similar test for overlapping within organization B , by comparing the start and finish times in columns H , I , J and K. These results are in column O.

3. Then there are the cross overlapping tests for overlapping between organizations A and B , by comparing the start and finish times in columns B , C , D and E with the start and finish times in columns H , I , J and K. Next , we compare the start and finish times in columns H , I , J and K with the start and finish times in columns B , C , D and E. These results are in columns T and U.

4. The overall result for overlapping , by combining all of these 4 results is in column S.

Once you verify that these results in column S are indeed correct , we can go on to calculate the overall overlapping hours , only for those cases where there is an overlap , instead of doing the calculations for all the cases ; it may be easier to verify the calculations for a small subset of your data , though in the data you have posted , it seems that the cases of overlap far outnumber the valid ones.

Narayan

Attachments

• 32 KB Views: 15

trizahler

New Member
Hello Narayan, thank you so much for the reply and the assistance! I cut down the amount of data and also worked to ensure there should be no overlaps. I still am seeing some "True" statements in the overlap columns....which is odd since I changed all the hours worked to not overlap.

I have put the minimized data on Sheet 2

Do you have any ideas as to why all overlaps statements are not "False" ? Thank You! - Tom

Hi ,

Narayan

NARAYANK991

Excel Ninja
Hi ,

My extension of the original formula , to cover more than one organization , was wrong.

I have corrected this.

Narayan

Attachments

• 80.4 KB Views: 14

trizahler

New Member
Hi Narayan,

This works perfectly now! Thank you so much for your insights into these formulas and work on this!

Is there a way to calculate the total overlapping hours?

Hi ,

My extension of the original formula , to cover more than one organization , was wrong.

I have corrected this.

Narayan

NARAYANK991

Excel Ninja
Hi ,

Give me some time to see if I can come up with a formula.

Narayan

trizahler

New Member
Of course, there is no rush at all. I am just happy to have your assistance on this project. Thank you Narayan.

Hi ,

Give me some time to see if I can come up with a formula.

Narayan

trizahler

New Member
Hi Narayan and Everyone,

I am working with a colleague to come up with a good spreadsheet to identify when an employee might be trying to work at two jobs at once.

Thank you Narayan for coming up with a way to identify if there are overlapping times for employees.

My colleague also came up with another version for less complicated cases of timecard fraud.

If anyone might have some time to compare and contrast these two spreadsheets and formulas to each other. And advise which might be the best for our use it would be most appreciated.

Thank you again, Narayan and everyone.

NARAYANK991

Excel Ninja
Hi ,

Sorry for the delay in replying.

I have still not looked into the issue of determining the overlap time , but on the issue of detecting whether or not there is an overlap , the formula has been revised so that it remains the same over an entire column , irrespective of how many names and entries there may be.

See the attached file.

Narayan

Attachments

• 22.5 KB Views: 26

trizahler

New Member
Hi Narayan, no apologies needed, we sincerely appreciate your assistance on this formal development. I do see now that the formula can detect an overlap over the entire column. Again we sincerely appreciate your assistance on this project. What were your thoughts on the other formula we came up with? No rush to get back to me. Best, - Tom

NARAYANK991

Excel Ninja
Hi Narayan,
Hi ,

If we look at the data in the first 4 rows in Sheet1 , it is :

11-05-2010 ... 00:45:00 ... 11-05-2010 ... 01:45:00
11-05-2010 ... 02:45:00 ... 11-05-2010 ... 03:00:00
11-05-2010 ... 03:45:00 ... 11-05-2010 ... 03:00:00
11-05-2010 ... 03:00:00 ... 11-05-2010 ... 05:00:00

for the first organization.

For the other organization , it is :

11-05-2010 ... 00:45:00 ... 11-05-2010 ... 03:45:00
11-05-2010 ... 02:45:00 ... 11-05-2010 ... 03:00:00
11-05-2010 ... 03:45:00 ... 11-05-2010 ... 03:00:00
11-05-2010 ... 03:00:00 ... 11-05-2010 ... 05:00:00

Can you work out what should be the overlap hours based on just this data ?

Only if we know what the result should be can we arrive at an Excel formula.

Narayan

grindryte

New Member
I think I found a possible solution here:
http://www.mrexcel.com/forum/excel-questions/709968-how-calculate-amount-overlapping-time.html#post3503049

Modifying the posted formula, I came up with the attached solution.
Hi Luke M and Narayan,

I tried your formulas in the attached sheet Luke M and it appears to have failed to pick up nearly every overlap when I played with it. It seemed to work when the overlaps were in consecutive lines, but as soon as I mixed the technician or left a row between overlapping times, it didn't pick it up. It only counted row 2 and row 9 as overlaps, when in fact there were 9 more highlighted in pink in Column A. The total overlap in cell L2 is 4 hours, but the total should be 40 hrs as shown in cell N2. Are you able to fix the formulas in the attached spreadsheet to work correctly or provide feedback as to how I can make it work?

I am a Chartered Accountant building a project timesheet to measure train productivity rates on our state's rail network. BJ003 and BJ005 refer to trains working at overlapping times in this example. This is a pretty cool tool that will go a long way to helping cost projects and plan work on a large scale that will save taxpayers money and help keep trains running. Just wanted to let you know your time overlap formula is being put to good use, so thanks for posting it.

Attachments

• 14.3 KB Views: 4

grindryte