can anyone help?
I've been tinkering with this for two days off and on.
Here's what I've come up with:
First, your fields should be timestamp fields instead of time fields. These fields record the date and time and you can compute elapsed time simply by subtracting them, which gives you the elapsed time in seconds. There's no need for special code to handle intervals that span the midnight hour.
( TimeEnd - TimeStart ) / 3600 will then compute your elapsed time in hours.
Using TimeStamp fields, an over time hours calculation can be defined this way:
Let ( [ OTE1 = GetAsTimestamp ( GetAsDate ( TimeStart) & " 8:30" ) ;
OTE2 = OTE1 + 86400 ;
OTS2 = GetAsTimestamp ( GetAsDate ( TimeStart) & " 15:30" ) ;
OTS1 = OTS2 - 86400;
TSO1 = Max ( OTS1 ; TimeStart );
TEO1 = Min ( OTE1 ; TimeEnd );
TSO2 = Max ( OTS2 ; TimeStart );
TEO2 = Min ( OTE2 ; TimeEnd )
Case ( TimeStart > OTE1 and TimeEnd < OTS2 ; 0 ;
TSO1 < TEO1 ; ( TEO1 - TSO1 ) / 3600 ;
( TEO2 - TSO2 ) / 3600
) // end case
) // End Let
The calculation checks to see if any part of the specified time stamp interval overlaps the over time period from two different days in order to handle all possible situations.
I wouldn't be supreised if there were a simpler calculation for this, but this does appear to produce the correct OT hours for your specified time interval. You can also replace the constants in quotes with references to fields to make this a calculation you can update without having to modify the field definition.
86400 seconds = 1 day and 3600 seconds = 1 hour.
PhilModJunk, I don't know how to thank you enough!!! This is AWESOME -- Thank you, Thank you, Thank you!!!