Need formula to calculate meal penatlies

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 30 minutes of penalty (or .5) before it goes up by .1 for each 6 minutes, and all times should be rounded up to the nearest 6 minute mark.


For example — if a meal is delayed by 12 minutes. There's no meal penalty.
If they break at the 13, 20 or 30 minute mark it's .5
and if they break at the 31 minute mark it's .6

I'm trying to build a formula that will minus the time in B2 from C2 and put the decimal in Column F if one is required.

	A	B	C	D	E	F
1	Name	Start	In	Out	Wrap	MP
2	Steve	0700	1313	1343	1800

Thanks so much for your help.

try this formula:

Formula:

Please Login or Register  to view this content.

That is exactly what I was looking for.

Thank you!

I've run into a problem that I hadn't noticed before, every 6th minute should be included in the previous .1,

so basically if we started at 7am and broke for lunch at 2pm, it should read 1.0. Right now the formula is giving me 1.1

try this:

Formula:

Please Login or Register  to view this content.

this one's a little shorter:

Formula:

Please Login or Register  to view this content.

The longer formula works like a charm, thanks!

If I wanted to modify it so that it starts calculating right after the 6 hour mark with no grace, what would I need to modify? I'm just trying to understand how it works.

to start from 6 hour mark, with a .1 for every 6 minutes, try:

Formula:

Please Login or Register  to view this content.

That works right up to the 6 minute mark, where it switches to .2 for 6 minutes over. It should still read 0.1 and only switch over to 0.2 at the 7 minute mark.

try:

Formula:

Please Login or Register  to view this content.

That's got it. Thanks!