Need help tweaking a formula calculating overtime

[Rift_tide]

I have a formula to calculate meal penalty between 2 cells. Cell C2 is the time lunch starts and Cell B2 is the in time of the day.
How MP works is that you get 12 minutes of "grace time" if you go over 12 minutes it's automatically 30 minute penalty.

So for instance if we started at 7am and broke for lunch at 1:12PM it should be 0.0
if we break any time between 1:13 and 1:30PM it should be 0.5
between 1:31 and 1:36PM should be 0.6
ect.

The problem I'm having is any multiple of 6 once we're dealing with penalty goes to the next one up. So if I put we broke at 1:36PM it calculates to 0.7 instead of 0.6. And I'm not sure how to fix it.

=IF(C2-B2<=TIME(6,12,0),0,IF(C2-B2<=TIME(6,30,0),0.5,QUOTIENT(C2-B2-TIME(6,30,0),TIME(0,6,0))*0.1+0.6))

Thanks for any help!

[cbatrody]

Hi,

Can you post a sample file in excel format please? Make sure your desired results are shown, mock them up manually if necessary. Remember to desensitize the file by removing all confidential information before upload!

See the following URL for help on how to upload a file.
http://www.excelforum.com/members/da...ch-a-file.html