# Trying to build a formula to calculate Lunch & Dinner Penalties on a film set

1. ## Trying to build a formula to calculate Lunch & Dinner Penalties on a film set

Hi there!

I have come across several other posts looking for a similar formula, but not quite what I am needing help with.

I am trying to find a formula that will calculate lunch and dinner penalties which occur 6 hours and 1 minute after the crew member's call time. And they continue to add up, 1 every 30 minutes, until the crew breaks for lunch.

I've attached an excel file as an example.

2. ## Re: Trying to build a formula to calculate Lunch & Dinner Penalties on a film set

In H4 copied down (formatted as General)
Formula:
`Please Login or Register  to view this content.`
In I4 copied down
Formula:
`Please Login or Register  to view this content.`
Does that do what you wanted?

3. ## Re: Trying to build a formula to calculate Lunch & Dinner Penalties on a film set

That's a good formula, ChemistB! First time seeing the use of double-quotes in a formula like this. Here's a Rep for you.

Just wonder, can you use CEILING instead of FLOOR and dropping the "+1" at the end of the formula?

4. ## Re: Trying to build a formula to calculate Lunch & Dinner Penalties on a film set

Joseph, the simple way is to try it yourself, then you can learn. i think it will work with ceiling but the "6:01" needs to be "6:00"
=CEILING(E7-D7-"6:00", "0:30")/"0:30"

5. ## Re: Trying to build a formula to calculate Lunch & Dinner Penalties on a film set

Try this
In K4, lunch penalties

=CEILING((E4-D4-"6:01")/ "0:30",1)

In L4, Dinner penalties.

=CEILING((G4-F4-"6:01")/ "0:30",1)

6. ## Re: Trying to build a formula to calculate Lunch & Dinner Penalties on a film set

That's right, thanks davsth!

7. ## Re: Trying to build a formula to calculate Lunch & Dinner Penalties on a film set

Thank you everyone, out of the numerous threads with a similar request, this was the first that was what I needed.

Will save me HOURS over the long run.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1