Need formula to help calculate meal penalties for film timecard/production report

I need a formula to calculate the amount of meal penalties for a film shoot based off of start time and lunch in time. Here are the assumptions on how meal penalties work:

- meal must be provided at the 6th hour, otherwise it's .1 for ever 6 minute increment. There's a 12 minute grace period before the first meal penalty begins, if it goes beyond the grace period it's automatically 1 Penalty(written as 1L) up until 30 minutes, afterwhich the amount of penalties increases.


For example — if work started at 8:00AM, lunch is owed at 2:00PM. If the lunch meal is delayed by 12 minutes (aka 2:12PM). There's no meal penalty.
If the crew breaks at the 2:13, there is a lunch penalty and would be written as 1L. If the crew breaks at 2:31, it now becomes 2L. 3:01, 3L. etc.

I'm trying to build a formula that will calculate if the time in D2 is 6 hours and 13 minutes passed the time in C2, then G2 will equal 1L, and for every situation after becomes 2L, then 3L.


A B C D E F . G
1 Name Call In Out Wrap MP
2 Steve 7:00A 1:13P 1:43P 7:00P ??



Thanks so much for your help. Hope this makes sense.

Hello, FDibbins.

Thank you for your note and congrats for the excellent job you guys do in this Forum!

Here is an explanation of how my formula works.

I use nested IF functions to first determine whether the amount of time is under the tolerance range. If not, I use IF+AND functions, combined with SUM function to determine whether elapsed time beyond tolerance is within the first and second half-hour increments range. Then I use QUOTIENT function to determine the number of half-hour increments occurred past the first two increments. The QUOTIENT result is used to count the number of fines. Finally, I use the concatenating operator “&” to include the character “L” as requested by Forum user.