Hi guys

I've created a small database to track my hours as an independent contractor in the entertainment industry. I have everything working great and want to implement one more feature into my time calculations, which I think is a little more complicated. I have been doing this manually so far and then adjusting my "Total Pay" with an overwrite field.

Currently I'm tracking time as decimals (i.e. 1:15PM is 13.25, etc). In my industry we have what are called meal penalties which an employee can start incurring if they haven't been broken for lunch within 6 hours and again if they're not broken for a second lunch 6 hours after the first lunch ended. 1 (one) meal penalty accounts for a 15 minute block of time, so if you start at 6am and aren't broken for lunch until 1pm, thats four meal penalties. If you again aren't broken at 7pm, respectively, you then start incurring more penalties until they either break you for the 2nd lunch or wrap you, and so on. Typically it's more common for meal penalties to be incurred at the end of the day (they make you work while eating your second meal) and we'll rack up meal penalties up until they finish us for the day ("wrap").

Ideally, I'd calculate how many meal penalty blocks I've incurred as a standard number. 1, 2, 3, 4, 5, etc...

Currently I have the following fields calculating my days:

((Invoice Terms)) Pay Rate

((TimeCard)) Call, Meal 1 Out, Meal 1 In, Meal 2 Out, Meal 2 In, Wrap, Total Hours, Total Pay

I want to add:

((Invoice Terms)) MealPenalty Rate

((TimeCard)) MealPenalties Incurred

And then I can add the meal penalty pay into the total pay appropriately. My main concern right now is just calculating the blocks of time.

Any help in calculating this penalty dealio my industry has so gratefully provided us as contractors would be great!

Try this as your starting point - it calculates the number of penalty blocks for the first period:

Let ( [
period1dur = Min ( Meal1Start ; Meal2Start ; WorkEnd ) - WorkStart ;
penalty1dur = Max ( 0 ; period1dur - 6 )
] ;
4 * penalty1dur
)

This assumes the input fields are decimal hours, rounded to nearest quarter.

3 hours ago, comment said:

Try this as your starting point - it calculates the number of penalty blocks for the first period:

Let ( [
period1dur = Min ( Meal1Start ; Meal2Start ; WorkEnd ) - WorkStart ;
penalty1dur = Max ( 0 ; period1dur - 6 )
] ;
4 * penalty1dur
)

This assumes the input fields are decimal hours, rounded to nearest quarter.

Comment

Works a charm! 

I can definitely work off of this. Would you suggest calculating the second block of time in a separate field?

3 minutes ago, madman411 said:

Would you suggest calculating the second block of time in a separate field?

No, why would I?

Alright. I think I have it, but I've become a little dazed while trying to debug my calculation while thinking in standard time. As usual I overcomplicated things in my head...

Comment, please grade:

Let ( [
period1dur = Min ( Meal1Start ; Meal2Start ; WorkEnd ) - WorkStart ;
penalty1dur = Max ( 0 ; period1dur - 6 ) ;
period2dur = Min ( WorkEnd ; Meal2Start) - Meal1End ; 
penalty2dur = Max ( 0 ; period2dur - 6 )
] ;
4 * penalty1dur + 4 * penalty2dur
)

 

If I understand correctly, the second period does not exist unless the first meal actually occurred. If so, you need to make:

period2dur = If ( Meal1Start ; Min ( WorkEnd ; Meal2Start) - Meal1End ) ;

Otherwise you will be double-billing for the second period's overtime.