calculating elapsed time within certain range
I need to calculate the elapsed time (or portion of) that occurs between a specific time, 15:31 to 08:29.
My current fields are: StartTime, EndTime, and Total Reg Case TIme (this calculates the elapsed time between the StartTime and EndTIme)
- so I want to make a new field called, Total OT Case Time, which will calculate any portion of elapsed time that falls outside of the normal business hours (08:30 to 15:30) from the StartTime and EndTime fields.
I'm new to Filemaker so this calculation has me totally stopped, I don't know how to make this happen, can someone please assist?
Thank you very much.
PS - Just so everyone knows what I'm looking at: My StartTime and EndTime fields are just Time fields set to military time. And my Total Reg Case TIme field is the following calculation:
(Round(Hour( EndTime - StartTime ) + Minute( EndTime - StartTime ) / 60;2)+If(GetAsNumber(EndTime) <= GetAsNumber(StartTime);24;0)) * 60
This calculation outputs the elapsed time in minutes and counts time that starts on one day but ends past midnight (the new calculation I'm seeking will have to do the same).
The result I need will have to count any portion of time that starts before 08:29, but stops counting at 08:30, even if the endtime goes beyond that time; and counts any portion of time that goes beyond 15:31, even if the starttime was prior to 15:30, but does not count the time prior to 15:31 -- for example, if StartTime is 14:30 and EndTime is 18:30 then Total Reg Case Time (the filed I already have) results 240 minutes and Total OT Case Time (the field I'm looking for) will result 179 minutes...