Converting Excel formula to FM Calculation

[madman411]

Hi All. Attempting to implement an Excel spreadsheet I have been using into a newly designed FileMaker database, but the Excel sheet isn't without limitations. The attached Excel spreadsheet file was provided to me to help start the process. The attached file is a timecard, however what I'm doing doesn't require calculation of wages - just tracking departmental daily film industry hours worked in "clicks" (tenth of an hour, military time) so we can submit the report for our department to our manager at the end of each day. I need help repurposing the calculation for meal penalties, particularly Union meal penalties.

Please see Excel formula below exampling the calculation for Meal Penalty 2 on the 1st Day:

=IF(AC11="W",0,IF(IF(ISNUMBER(AY11),BE11-AY11,IF(ISNUMBER(AS11),AS11-AM11,IF(ISNUMBER(AM11),BE11-AM11,BE11-AC11)))>6.9,3,IF(IF(ISNUMBER(AY11),BE11-AY11,IF(ISNUMBER(AS11),AS11-AM11,IF(ISNUMBER(AM11),BE11-AM11,BE11-AC11)))>6.4,2,IF(IF(ISNUMBER(AY11),BE11-AY11,IF(ISNUMBER(AS11),AS11-AM11,IF(ISNUMBER(AM11),BE11-AM11,BE11-AC11)))>6,1,0))))

In the Excel spreadsheet, under the UnionTC tab, on the "MEAL PNLTY" column, you'll see each day has two cells calculating meal penalty violations. I'm struggling converting Meal Penalty 2, or the lower cell of the two cells. These occur twice in a 30 minute period where a break hasn't been given within a period of 6 hours or less. I also see in this example that the calculation only factors up to 6.9 hours (or three penalties) where it would be great to also calculate penalties accrued > 7.0 hours past call time or the previous meal penalty ending.

Although it's shown on the spreadsheet, below you'll find what each cell number in the formula is referencing:

AY11 - Meal 2 In
BE11 - Wrap (end time)
AS11 - Meal 2 Out
AM11 - Meal 1 In
AC11 - Call (start time)

I managed to convert Meal Penalty 1 using Case () and it seems to be working correctly, but Meal Penalty 2 is causing me some major headaches.

In a nutshell, there are union regulations that stipulate a meal break must be given within six hours of Call and/or within 6 hours of the previous meal break. Sometimes jobs do "french hours" in which case no official break is given, and Meal Penalty 2 needs to calculate how many violations have accrued from Call time to Wrap time in the absence of meal breaks being entered.

Any help appreciated

EP basic forms.xlsx

[comment]

16 minutes ago, madman411 said:

Please see Excel formula below

Sorry, I am not going there.

 

16 minutes ago, madman411 said:

there are union regulations that stipulate a meal break must be given within six hours of Call and/or within 6 hours of the previous meal break.

This should be easy to verify by calculating the differences between call and meal break starts. I am afraid I've lost you after that.

 

Edited February 9, 2023 by comment

[madman411]

2 hours ago, comment said:

This should be easy to verify by calculating the differences between call and meal break starts. I am afraid I've lost you after that.

@comment - sorry, sometimes I still get confused with how they calculate all of this stuff.

On union contracts there are penalties that are in place for the employer if they don't provide a break every six hours. Despite "clicks" (tenths of an hour) being standard for time reporting, these are usually calculated in 15 minute increments and have different fees tied to them, i.e. after six hours worked with no break, you'll accrue one penalty. Once hour 6.4 hits another penalty will be added if still not broken for lunch (so two meal penalties at this point), and the cycle continues until a break. This would be Meal Penalty 1.

The same is applicable on the back end. So after your first meal break, penalties will occur once the 6 hour mark has surpassed again and you've not been dismissed for the day or a second meal has been provided. This would be Meal Penalty 2, and so on. Sometimes you aren't broken for a break at all, so a meal violation begins from hour six until you are dismissed for the day. If you work a 12 hour day without breaks then 24 meal penalties will be paid to the employee for the day.

[comment]

So where exactly are you stuck? If you know all the rules, what can't you calculate? (If you don't, then I cannot help you).

Hint: https://en.wikipedia.org/wiki/Rubber_duck_debugging

 

[madman411]

40 minutes ago, comment said:

So where exactly are you stuck? If you know all the rules, what can't you calculate? (If you don't, then I cannot help you).

Hint: https://en.wikipedia.org/wiki/Rubber_duck_debugging

 

 I appreciate your encouragement.

Meal Penalty 1 was easy to decipher, but I'm struggling with MP2 because it is more complex and covers the entire work day rather than just the first half.

If I had a question for your area of expertise it would be how to eliminate the need for assigning each penalty individually within the calculation - rather than a long Case () function checking if equations return >6, >6.4, >6.9, etc, how would it be possible to calculate the results through a formula that determines that result criteria automatically for anything over 6 hours, and the respective violation times thereafter? Each penalty begins at the 6 hour mark, another at minute 15, another at minute 30, another at minute 45 and another at the top of the hour, and continues. However, we enter times as tenths of an hour.

In the event of a day without meal breaks, I'd have to write a Case function that acknowledges 24+ different equations (assuming we only work 12 hours... sometimes it can be much longer). There must be a more simple way for the computer to determine these.

[comment]

20 minutes ago, madman411 said:

There must be a more simple way for the computer to determine these.

I suppose there is - but I don't fully understand your explanation. It seems to me that if you subtract 6 hours from the total duration and divide the result by 15 minutes, then the ceiling of that result will be the number you're looking for.

26 minutes ago, madman411 said:

However, we enter times as tenths of an hour.

I believe you mean "decimal hours". That's easy to translate to FM concept of time  - for example, both GetAsTime ( 4.2 * 3600 ) and Time ( 4.2 ; 0 ; 0 ) return the time of 4:12:00.

Or, if you prefer, you could stay with decimal hours for the entire calculation - for example:

Let ( [
start = 4.2 ;
end = 12.6 ;
duration = end - start ;
overtime = Max ( 0 ; duration - 6 )
] ; 
Ceiling ( overtime / .25 )
)

returns 10.