Calculating Meal Penalties Formula

Hello. I am very new to Excel and the IF functions. I am hoping to get some help with a formula I am trying to create. Essentially I am trying to create something that will calculate how many "Meal Penalties" someone has. A meal is required to be given 6 hours into the workday. There is a 12 minute grace period before the first penalty is incurred. After that, there is an additional penalty every 30 min. EX: If work starts at 6:00 am and lunch is taken at 12:12 pm it would be 0 penalties. 12:13pm = 1MP. 12:31 PM=2MP. 1:01 PM=3MP and so forth.

In C2 I have my work start time, in D2 is the time we stopped for lunch. Trying to populate this in J2. The time values are all written in the Time Format.

Thank you in advance!

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Please respond to the help offered in your previous thread.

I used some helpers (L2:M2) to make the process more understandable.
L2 displays the actual meal start time and is populated using: =SUM(C2,6/24)
M2 displays the grace meal start time is populated using: =SUM(C2,6/24,12/1440)
J2 is populated using: =IF(D2<=M2,0,ROUNDUP((D2-L2)/(30/1440),0))
Let us know if you have any questions.

@bdooley87

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread