Timecard formula questions for motion pictures...

[dwaynet19]

I work in payroll for movies and have an excel time card that Ive been working on to help make things a bit easier.
There are many different unions and rules to deal with, but 1 section that I am asking for help on is regarding meal penalties.

I wish I could add an attachment so you can see what im talking about

To get a general idea:

the hour is calculated in tenths. so every 6 minutes is 1/10....and in military time

6:03 am = 6.1
6:07 am = 6.2
6:08 pm = 18.2
6:31 pm= 18.6

so a time card looks like this: (roughly)

6.0 in 12.0-12.5 lunch 18.0 wrap

meal penalties are calculated from in time until lunch leave(12.0)
and from return from lunch (12.5) to wrap.....these are not compounded

in the above, in time is 6.0 and lunch is 12.0 a difference of 6 hrs.(no meal penalties)
Meal penalties start at 6.3 and are every 1/2 hour in the front and start at 6.6,every 1/2 hour in the back end.

mp chart:
1= 7.50
2= 17.50
3= 30.00
4 and on is an additional 12.50 for each one.

in this example:

6.o in 13.0-13.5 lunch 22 wrap
6.0-13.0 = 7 hours (anything over 6 is a mp) so 1 hr over = 2 meal penalties $ 17.50 for the front....

13.5 - 22 = 8.5 hrs -6 = 2.5 hours = 5 meal penalties = $55.00 for the back

total day is $72.50 in meal penalties for the day.

This is a formula I have

=IF((I9-G9)-6<0.01,0,IF((I9-G9)-6<0.6,1,IF((I9-G9)-6<1.1,2,IF((I9-G9)-6<1.6,3,IF((I9-G9)-6<2.1,4,IF((I9-G9)-6<2.6,5,IF((I9-G9)-6<3.1,6,IF((I9-G9)-6<3.6,7,8))))))))

It only allows me to go up to 15 mps (this only has 7)and I would like it to go up to 20.
Is there a way I can go to 20?
Is there a better way to calculate this formula instead of what I have?
Is it easier for the formula to add 12.50 to every 1/2 hour after 2? (3 on)

 

[Jerry Sullivan]

Is there a better way to calculate this formula instead of what I have?
Is it easier for the formula to add 12.50 to every 1/2 hour after 2? (3 on)

If you're just calculating the cost and not the number of penalties, your formula could be simplified somewhat as you considered by adding $12.50 per 1/2 hour after 2 mp.

However for payroll, I'd recommend using a lookup table for both the number and cost of penalties.

Two benefits are:

**Easy of maintenance: It is easier to modify your table than the formulas (if the rates or terms are changed by Union contracts)

**Clarity: Payroll usually requires audit. I believe you have better QA/QC with a lookup table to embedded formulas.

Here's one way to do that:
Create a table for Front and another for Back. Name the ranges for clarity.

The table can go to 20 mp or more..the image below just shows the top rows.

Excel Workbook
	O	P	Q	R	S	T	U
8	Front	mp #	mp$		Back	mp #	mp$
9	0	0	$0.00		0	0	$0.00
10	6.01	1	$7.50		6.01	1	$7.50
11	6.3	2	$17.50		6.6	2	$17.50
12	6.8	3	$30.00		7.1	3	$30.00
13	7.3	4	$42.50		7.6	4	$42.50
14	7.8	5	$55.00		8.1	5	$55.00
Timecards

Now use VLOOKUP to find number of mps and/or cost of mps.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
8	Front mp #	Front mp $	Back mp #	Back mp $		Timecard
9	0.0	$0.00	0.0	$0.00		6.0	in	12.0	-	12.5	lunch	18.0	wrap
10	3.0	$30.00	5.0	$55.00		6.0	in	13.0	-	13.5	lunch	22.0	wrap
Timecards

#VALUE!

</td></tr></table></td></tr></table>


I'm a little confused about the point the first penalty kicks in for the Front. Based on your formula example, I'd looks like time over 6.01 is one mp and over 6.3 front/6.6 back is two mp. However you say that mps begin at 6.3. That's just a small detail though and you can adjust the table accordingly.

 

[dwaynet19]

wow this helps out alot!
I just have to play around with it more to learn how to insert the tables and the formulas.

Basically, there is a 12 minute grace period from call to the first mp. Thus is why the mps start at 6.3 (there is no mp for 6.1 or6.2) and at 6.6 on the back end because there is a 30 minute wrap/grace period....

Would you be interested in working with me to create a time card with formulas? I actually have a couple ideas for programs, but there is a lot of variables for the different unions that makes it pretty difficult for me...

 

[Jerry Sullivan]

I'm happy to hear that helped. Thank you for the feedback.

If you are wanting professional Excel consulting services, please contact the good people at MrExcel who host this Board. http://www.mrexcel.com/consult.shtml

 

[Bojanglez]

wow this helps out alot!
I just have to play around with it more to learn how to insert the tables and the formulas.

Basically, there is a 12 minute grace period from call to the first mp. Thus is why the mps start at 6.3 (there is no mp for 6.1 or6.2) and at 6.6 on the back end because there is a 30 minute wrap/grace period....

Would you be interested in working with me to create a time card with formulas? I actually have a couple ideas for programs, but there is a lot of variables for the different unions that makes it pretty difficult for me...

Grace is not automatic. It is normally used when trying to complete a scene or setup. If the A.D. does not call grace, it shouldn't be implied that one should be deducted from M.P.'s.