madman411 Posted February 8, 2016 Share Posted February 8, 2016 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 More sharing options...
comment Posted February 8, 2016 Share Posted February 8, 2016 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. 1 Link to comment Share on other sites More sharing options...
madman411 Posted February 9, 2016 Author Share Posted February 9, 2016 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 More sharing options...
comment Posted February 9, 2016 Share Posted February 9, 2016 3 minutes ago, madman411 said: Would you suggest calculating the second block of time in a separate field? No, why would I? Link to comment Share on other sites More sharing options...
madman411 Posted February 9, 2016 Author Share Posted February 9, 2016 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 More sharing options...
comment Posted February 9, 2016 Share Posted February 9, 2016 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 More sharing options...
Recommended Posts
This topic is 3127 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 accountSign in
Already have an account? Sign in here.
Sign In Now