• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Overlapping time formulas in excel (eliminate duplicate times on a timesheet)

grindryte

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

Attachments

  • Time Overlap_18.6.2019.xlsx
    14.3 KB · Views: 9
grindryte
Before any formulas ...
Check Time overlap (2)-sheet...
There seems to be five different start-finish times.
Which overlapping are You interesting?
Could You add more colors right side to show those?
 

Attachments

  • Time Overlap_18.6.2019.xlsx
    17.7 KB · Views: 7
Thanks for sending your file. I appreciate your input. 4 of the 5 different start times do not have any hours of duration. Only 8am to 12pm on 12 May 2010 has times with a start and finish time beyond being blank times.

I guess I didn't explain what I'm ultimately trying to calculate. I am interested in the green highlighted overlap times in your Time overlap (2) file shown in column R. I wanted to know overlapping hours to calculate the unique hours. e.g:
BJ003 on 12 May works from 8am to 12pm (4 hours) and replaces 400m of track
BJ005 on 12 May works from 8am to 12pm (4 hours) and replaces 400m of track
Therefore Productivity is 800m in 4 hours (not 8 hours, as we eliminate the overlapping 4 hours and keep the unique hours).

In my first file (and your Time Overlap (2) file) there were 11 overlapping times of 4 hours each from 8am to 12pm and the formulas could not identify or isolate the unique time. The unique time should be 4 hours as 10 of those 4 hour times overlapped.

Does this help understand the formula I hope to use?

Cheers,
Brad
 
grindryte
hmm? why not?
row 28: BJ005 - 1136338 - 13-May-00 00:00 -- 14-May-00 00:00 = 24hrs

Is Your given data from real or something random?
In both cases ... that won't look useful; eg REFWOs 1042667 & 10427729.
Which of those are information ... and which misinformation.
How many rows data would You normally use? ...30?
If a lot of rows then I won't use formula to solve ... something.

Your original was: ... I need to eliminate the times when they both operate ...
... and surprise, now: I wanted to know overlapping hours to calculate the unique hours.
Do those give same result?

Could You find out --- what are You looking for?
 
grindryte
hmm? why not?
row 28: BJ005 - 1136338 - 13-May-00 00:00 -- 14-May-00 00:00 = 24hrs

Is Your given data from real or something random?
In both cases ... that won't look useful; eg REFWOs 1042667 & 10427729.
Which of those are information ... and which misinformation.
How many rows data would You normally use? ...30?
If a lot of rows then I won't use formula to solve ... something.

Your original was: ... I need to eliminate the times when they both operate ...
... and surprise, now: I wanted to know overlapping hours to calculate the unique hours.
Do those give same result?

Could You find out --- what are You looking for?


HI Vletm.

I can use Overlapping or Unique hours to get the result.

If I use gross hours - Overlapping hours = Unique hours

Or if the formula gives me unique hours, that's fine.

I just need to identify when times overlap so they aren't included in my productivity calculation.

The data I provided was in a format that I use. It's not real data, but it is possible it may occur, I just input the times to test the formula before I go live with it.

Regards,

Brad
 
Hmm?
I've understood that You're interesting in 'those greens' and the formula.
Before 'greens' could find, I need clear answers.
The formula 'case' ... as I've written.
... if Your sample data is not as real as possible ... then ... would results also be random?
 
Hi Vletm,

I have attached a file with "Time overlap (3)" named sheet. It has data that was real. The times for BJ003 and BJ005 could potentially fall at any time of day or any length of duration up to 36 hours continuously if circumstances allow.

I would require around 30 rows for time data, as shown in the file.

What I am interested in is identifying the unique hours, which can be calculated by identifying the total regular hours minus the overlapping hours.

Does this give you enough info for the formula case?

Brad
 

Attachments

  • Time Overlap_25.6.2019.xlsx
    22.1 KB · Views: 8
grindryte
... still ... formula ... formula ... formula ...
Visual image ...
Which times are You really interesting?
Are those GREEN lines in chart those times or so?
 

Attachments

  • Time Overlap_25.6.2019.xlsx
    22 KB · Views: 6
grindryte
... next Sample ...
With [ Solve ] can see overlaps
With [ ReSet ] can edit data
That looks great, you've built a useful macro showing good visual presentation of the times. I can use this.
I have a requirement though for my end users, they can't use macros on the current excel version used on their ipads. Apple and Microsoft haven't integrated it yet, so unfortunately this won't work on an Ipad. Sorry to advise. This is why I've focused on using formulas to calculate.
 
grindryte
Where did You mentioned that someone would use this with eg iPad? It's well known 'unwanted' feature.
You asked formula ... formula ... to get many TRUE/FALSE, instead eg below result.
60985
 
Hi Vletm,

I thought there was an existing formula that could simply calculate time and minus overlaps. It appeared the original formula worked under certain circumstances, I didn't think it would take this many iterations, but appreciate your input. Is there a way of showing the numbers produced in your chart above without using a macro? i.e. the unique time.

Regards,

Brad
 
grindryte
Term ... the unique time
Do You mean times which are not 'blue'?
There are ... many of those.
> There is a way to show many things without using a macro. Note, show is bolded!
Could You give a visual sample of Your 'the unique time' -showing?
 
Hi Vletm,
Attached are 5 examples showing the production hours of each machine, the overlap hours and the production hours less overlap. This is in the "Productive hours examples" sheet. These examples show what I am trying to calculate, by only including overlapping times once. This is what I would refer to as a unique time, being only one of a kind, not duplicated.

I would like to be able to calculate productive hours less overlap, based on the data appearing in "Time overlap calculation layout". This is how the data will display.

Let me know if you have any queries about the examples.

Regards,

Brad
 

Attachments

  • Time Overlap_examples_3.7.2019.xlsx
    22.8 KB · Views: 14
grindryte ( Fri ... Wed )
You seems to changed something and
the last my sent chart was 'version-two'. I hided 'version-one'.

I would do something like this.
There should be all or almost all of Your ideas.
There would be reasons ... why some parts are as those are now...
Left side is data area.
Right side is [ SOLVE ]-button ( gotta use for refresh ) and chart.
I haven't checked all [im]possible cases! There could be some unwanted features.
 

Attachments

  • Time Overlap_25.6.2019.xlsb
    35.6 KB · Views: 21
Looks great, but it's a macro and I can't use it on the ipad versions of excel for the workers. If I could use it, I still can't see which cell gives me the the production hours number (Total 35:05 - Overlaps 12:30 = Difference 22:35). I need to be able to link that number/time. I tried linking it to pull StartDate, StartTime, FinishDate, FinishTime from another file but the macro failed.

Is there a way of calculating this time, without a macro?
 
grindryte
... the ipad versions of excel for the workers.
> then those should use eg laptops
OR
> You could solve 'something' without iPad and save that file ... then the results can SEE with iPad too.
I still can't see which cell gives me the the production hours number
> Did You watch that graph?
> Before #17 reply, You wanted 'only' overlaps.
> Your original thread is: eliminate duplicate times on a timesheet ... means hmm? ... windy!
>> 'the' macro works with that layout.

Is there a way...

There would be many ways ... this was my way.
 
Hi Vletm,

I was trying to calculate the overlaps because I had the total hours from each machine BJ003 and BJ005. The purpose of this has always been to calculate the overlapping times for the purpose of eliminating them. To "eliminate duplicate times" means the same thing . The purpose of what I was trying to achieve here on this thread is the same as what was being attempted on the previous thread here https://chandoo.org/forum/threads/calculate-overlapping-time-in-excel.16316/

Yes you achieved it, in your way, but it's not a formula that anyone can use or link their data to.

You've got some great excel skills, but we've drifted around the point of the thread and digressed to see your capabilities, but it's too complex for what should be a simple formula that shows a number that can be used. Are you expecting payment before you create a usable format? I can't link a chart to a formula for overlapping hours. The chart looks great, certainly would look good in a Monday morning meeting.

The workers have laptops but cannot carry laptops on a train track, so they have ipads or use iphones with excel. They complete these forms when working without internet, usually on or near a remote train track.

Regards,

Brad
 
grindryte
... trying to achieve here on this thread...
Sometimes, it's possible to use 'previous threads'.
... that anyone can use or link their data to.
Have You written above before? Whatever it would mean ... ?
You've asked something ... but seems many (needed) things are missing.
What would mean a simple formula?
1+2=3 ? Many things are possible, but ... I won't use Your used term 'simple'.
payment
That Your verified graph-output has Your named details - or how?
Payments are always welcome, especially then 'windy cases'.
I can't link a chart to a formula for overlapping hours.
hmm? What would above mean?
so they have ipads or use iphones with excel.
As I wrote You could solve 'something' without iPad and save that file ... then the results can SEE with iPad too.
They would have it ... and the best part ... they could keep it same.
Now, You're writing about forms ... yes ... that chart could use with forms too.
What would be Your challenge with that?
 
Back
Top