With overlapping time ranges, calculate "net" time

[Alex Taylor]

Hi guys,

I've been searching through the forums here to get some insight into a problem I've been having, but I can't quite find enough info to figure it out.

I have a FileMaker solution that tracks time spent on a job. Each job can have multiple time entries logged for it, and since a few people might be working on the same job, those time entries can overlap.

For each job, I want to calculate the net amount of time that was spent. In other words, I want to ignore any time overlaps and calculate the total time spent in general, not just total up each separate time entry.

Visualized, my data might look like this:

9 ------- 10

9 ------- 10

			  11 ----------- 1

                                  12 ------------ 2

 

In the above example, I would consider the net time as being 4 hours, since some of the time logs overlap.

I've attached a very basic idea of my database with some sample data. I feel like there should be a way to do this with self-joins and a couple calculations, but I still haven't gotten my mind wrapped around it. Any help would be much appreciated!

OverlappingTimes_Demo.fp7.zip

[Tom Elliott]

I think the attached does what you want

cheers

Tom

Ooops - forgot to attach file!

OverlappingTimes_Demo_v2.fp7.zip

[comment]

I feel like there should be a way to do this with self-joins and a couple calculations

I don't think there is a simple way to do this. Before getting into it - what is the significance of the result? If Adam worked for 2 hours and Betty for 3, there are 5 man-hours in the project - regardless of overlaps.

I think the attached does what you want

Well..

OverlappingTimes_Demo_v2.zip

[Kilyaaan]

I dont think that this can be solved with just using relations on the actual timestamps.

So to solve this I ended up with adding a row field that i populate with serial numbers and then base my relations on that.

And i got a second problem, trying to calculate the effective timestamps, i need to compare with the the related previous timeLogs ditto, and FileMaker see's this as trying to have a calculation that is based on itself, even if its in another TO. So i had to make an autoenter calculation instead.

So all in all this make the solution not very dynamic.

But when you push the button It seem's to come up with the right result.

...... Well the site was not responding, so I used the time to figure out a way to make it dynamic, and thought that a Custom funtion should do the trick.

So I added a custom function to the solution, leaving the rest as it was, a bit messy to look at, but hopefully it can helpyou figure out the mechanics.

The custom function dosnt need any of the ekstra fields, besides the result field.

--- been thinking - the current Custom function is way to error prone, if there is missing some of the timestamps, the result will be invalid.

Sorry I wont have time until late sunday to make a new one.

OverlappingTimes_Demo_v3.fp7.gz

[Tom Elliott]

Well .. how about ..

OverlappingTimes_Demo_v3.fp7.zip

[comment]

Well .. how about ..

Well...

OverlappingTimes_Demo_v3x.zip

[Kilyaaan]

A new calculated timspan field and a custom function, and then sorting the portal ascending start time.

OverlappingTimes_Demo_vCF.fp7.gz

[Tom Elliott]

Well...

Yes, I figured that out for myself shortly after posting. Ah, well

[Alex Taylor]

Wow, thanks for the replies everyone! This was exactly what I was looking for. I haven't got into Custom Functions too much yet, so this is great to have some solid examples to learn from. Thanks for the input!

I don't think there is a simple way to do this. Before getting into it - what is the significance of the result? If Adam worked for 2 hours and Betty for 3, there are 5 man-hours in the project - regardless of overlaps.

It's going to be used as a way of determining total time if multiple people are involved in the same task. Let's say Adam and Betty were both in a meeting for 2 hours, and they separately log their time for that. We want to be able to say that 2 "company" hours were spent in the meeting, instead of 4 cumulative "man hours."

[Tom Elliott]

Ok, try again.

I really think I've got it this time - with the proviso that unreasonable time log entries are likely to lead to an unreasonable 'net time' for the job - but as always I'm ready to be shown the error of my ways

OverlappingTimes_Demo_v4.fp7.zip

[comment]

Let's say Adam and Betty were both in a meeting for 2 hours, and they separately log their time for that. We want to be able to say that 2 "company" hours were spent in the meeting, instead of 4 cumulative "man hours."

Perhaps this would be better handled on the data entry side, by entering "company hours" separately from man-hours.