Date calculation issue

[comment]

I suggest you post your date question separately, as it is unlikely that anyone else in the future will be interested in this particular combination of problems.

When you do, please clarify if it is possible for a shift to span more than 2 periods - e.g. start at 7:00 PM and end at 9:00 AM the following day.

Also, please update your profile to reflect your version and OS so that we know what you can use.

 

[Alan85]

Good day. I am in the board of a voluntary red-cross organization and work on the project of creating a database to summarize all relevant information into one platform. In the creation of the database I am struggling on the following issue:

One part of the database is to record the medical services the organization offers at events. On those events we work in different shifts. Displaying those shifts in a portal is no problem. Now the part where I am struggling: We distinguish between "Day-Hours" (shifts between 8.00 AM and 7.59 PM) and "Night-Hours" (shifts between 8.00 PM and 7.59 AM) for our invoicing rates. So, when a shift for example lasts from 6.00PM until 11.00PM I need Filemaker to automatically calculate how many "Day-Hours" and "Night-Hours" are within this shift - In this case the result would need to show "2" in the field for "Day-Hours" and "3" in the field for "Night-Hours". There are also shifts possible that will last until the next day, e.g. shift 1 from 2.00 PM to 6.00 PM, shift 2 from 6.00PM to 10.00 PM and shift 3 from 10.00PM to 2.00AM. 

How do I need to set up the calculation (or script), so that Filemaker automatically calculates those values?

[Ocean West]

FYI i tried to merged the topic - sorry the system got the sequence backwards.

[comment]

46 minutes ago, comment said:

please clarify if it is possible for a shift to span more than 2 periods - e.g. start at 7:00 PM and end at 9:00 AM the following day.

I don't think my question has been answered. To put it more simply, can a shift last more than 12 hours?

 

[Alan85]

Sorry for misunderstanding. No, a shift cannot last more than 12 hours. The maximum of a shift is defined to 8 hours (guidelines and regulation of the organization)

[comment]

19 minutes ago, Alan85 said:

a shift cannot last more than 12 hours.

Ah, good. Then I think you can calculate the day hours as =

Let ( [
adjEndTime = EndTime + 86400 * ( EndTime ≤ StartTime ) ;
dayStart = Time ( 8 ; 0 ; 0 ) ;
nightStart =  Time ( 20 ; 0 ; 0 ) ;

dayOverlap = Case ( 
StartTime < nightStart ; 
Max ( Min ( adjEndTime ; nightStart ) - Max ( StartTime ; dayStart ) ; 0 ) ;
Max ( Min ( adjEndTime ; nightStart + 86400 ) - Max ( StartTime ; dayStart + 86400 ) ; 0 )
) 
] ;
dayOverlap / 3600 )
)

and night hours as = 

Let ( [
adjEndTime = EndTime + 86400 * ( EndTime ≤ StartTime ) ;
dayStart = Time ( 8 ; 0 ; 0 ) ;
nightStart =  Time ( 20 ; 0 ; 0 ) ;

nightOverlap = Case ( 
StartTime < dayStart ; 
Max ( Min ( adjEndTime ; dayStart ) - Max ( StartTime ; Time ( 0 ; 0 ; 0 ) ) ; 0 ) ;
Max ( Min ( adjEndTime ; dayStart + 86400 ) - Max ( StartTime ; nightStart ) ; 0 ) 
)
] ;
nightOverlap / 3600 )
)

where StartTime and EndTime are Time fields storing the start and end times of a shift. The results here are of type Number. If you want actual times instead of decimal hours, then do not divide by 3600 at the end and set the result type to Time .