I tried using the formulas in the attached sheet from another thread ( https://chandoo.org/forum/threads/calculate-overlapping-time-in-excel.16316/ ) and it appears to have failed to pick up nearly every overlap when I played with it. I've repeated the same date and times down the table to test the formulas and it's failed. 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.
Is anyone able to point me to a working version or edit 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. To accurately measure productivity ( Productivity formula = metres of track repaired / time) I need to eliminate the times when they both operate and link this into my summary page (not attached).
This is a pretty cool tool that will go a long way to helping cost projects and plan work on a large scale. Just wanted to get the formulas working correctly and thank the forum for developing the time overlap formula and posting it.
Cheers,
Brad
Is anyone able to point me to a working version or edit 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. To accurately measure productivity ( Productivity formula = metres of track repaired / time) I need to eliminate the times when they both operate and link this into my summary page (not attached).
This is a pretty cool tool that will go a long way to helping cost projects and plan work on a large scale. Just wanted to get the formulas working correctly and thank the forum for developing the time overlap formula and posting it.
Cheers,
Brad