Jump to content

Calculating blocks of time


This topic is 2971 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
)

 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

This topic is 2971 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.