Extract Time from Filter Range

[isaaccs]

I'm seeking a function or calculation (explained) that, given:

filterStartTime

filterEndTime

startTime

endTime

returns the quantity of hours/time present between the start time and the end time within the filter range.

So, for example, in a timesheet, we are only looking for 'overtime' hours worked between 1 am and 6 am...

filterStartTime = 1:00am

filterEndTime = 6:00am

employeeStartTime = 12:30am

employeeEndTime = 11:00am

This function would return 5 hours (the time between the filter values). Excuse my abusive labor practices, this is only a concept.

Any thoughts/functions/insights that anyone has used?

[comment]

Try:

Max ( Min ( RangeEnd ; EndTime ) - Max ( RangeStart ; StartTime ) ; 0 )

[isaaccs]

Works like a charm, many thanks for your assistance.

[mark s reynard]

Hi

I am working on a timesheet system and i just can't seem to get my around this calculation.

From entering to Time Fields

Start Time and End Time, i need to be able to show the following calculations

Basic Pay Hours

Night Rate Hours

This is how far i have got.

I have 2 fields which show the normal pay rate times as these can vary.

So BasicPayStart = 0700 and BasicPayEnd =2100

The following calculation sort of works until the EndTime is less than the StartTime ie they start work at say 21:00 and finish the next day say at 07:00

My Calculation Fields

BasicHoursCalculation

Max ( Min ( BasicPayEnd ; EndTime ) - Max ( BasicPayStart ; StartTime ) ; 0 )

NightHoursCalculation

Max ( Min ( EndTime ; BasicPayEnd ) - Max ( StartTime ; BasicPayStart ) ; 0 ) + Max ( Min ( EndTime; BasicPayEnd ) - Max ( StartTime ; BasicPayStart ) ; 0 )

I think i need to use the calculation using timestamps and where the endtime < starttime add a day to the calculation, but ive tried and tried and can't get it to work, doing my head in)

Any help greatly appreciated.

Thanks

[mark s reynard]

Max ( Min ( RangeEnd ; EndTime ) - Max ( RangeStart ; StartTime ) ; 0 )

This doesn't work if the endtime is less than the starttime, ie (starttime 21:00 end time 0700 (the next day)

Any ideas

Cheers

[comment]

If EndTime is less than StartTime, add 24 hours to EndTime before calculating the overlap (assuming a shift cannot exceed 24 hours). Or use timestamp fields.

[comment]

Answered here:

http://fmforums.com/forum/showtopic.php?tid/213248/post/354928/#354928

Please do not double-post.

[Lee Smith]

I merged your two topics.

Please do not double post your Questions.

[mark s reynard]

Sorry about double posting

The answer however I can;t seem to make it work,ive added 24 hours as follows should endtime < starttime.

The calculation following your instruction, looks as follows

If(EndTime

What I would like to happen,

if starttime is 22:00 and endtime is 09:00 then there should be a duration of 11 hours, so the breakdown should be

22:00 until 7:00 (Night Hours) ie 9 hours

07:00 until 9:00 (Day Hours) ie 2 hours

The solution as amended gives 1hr as normal hours, as this is the normal hours calculation

any assistance appreciated

Thank, again

[Lee Smith]

Have you tried using Timestamps for your dates and times?

Lee

[comment]

There are two issues here, the first one being that your range crosses midnight, too. This can be solved by making RangeEnd equal to 31:00:00 instead of 7:00:00, or by changing the range to day hours, i.e. from 7:00 to 21:00 (you can always calculate the "other" part by subtracting the overlap from the total duration).

The other issue is more difficult. At least in theory, a shift can overlap the range TWICE. For example, a person can start at 6:00:00 and end at 23:00:00. Here there are TWO overlaps with the night range: one hour at the beginning of the shift (from 6:00 until 7:00) and two hours at the end (from 21:00 until 23:00).

The calculation above returns only one of the overlaps. If you cannot eliminate the possibility of the same shift overlapping the defined range (whether day or night) more than once, then this will not work for you.

[mark s reynard]

Yes, I tried with timestamps, just failed whatever I tried, I thought this would be easy, ouch. any help with timestamps would be appreciated.

[Lee Smith]

Post a [color:blue]copy of your file. Remove any confidential information, and then [color:red]zip the copy and post following this Link

Lee

[mark s reynard]

Hi I've almost got there using timestamps, please see attached zip file.

The problem i now have is with 2 overlaps. (I think)

Lets say the Normal range is 07:00 till 21:00

if i start at say 20:00 and finish at 08:00 the next day thats a 12 hours shift

So the calc should be 1 hour normal until 21:00, then 10 hours until 07:00 then a further normal hour 07:00 til 08:00.

Therefore the Result should be

Duration = 12

Normal Hrs = 2

Night Hrs = 10

My calc returns the following result

Duration = 12

Normal Hrs = 1

Night Hrs = 11

Any suggestions, greatly would be hugely appreciated.

Thanks

timesheet.fp7.zip

[comment]

How about:

timesheet2.zip

[mark s reynard]

many thanks

that seems to work brilliant