+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    04-21-2021
    Location
    New York City
    MS-Off Ver
    2018
    Posts
    2

    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.

    Thank you in advanced!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

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

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

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,544

    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. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,009

    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. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,297

    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)
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,544

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

    That's right, thanks davsth!
    Last edited by AliGW; 07-29-2021 at 03:46 AM. Reason: PLEASE don't quote unnecessarily!

  7. #7
    Registered User
    Join Date
    04-21-2021
    Location
    New York City
    MS-Off Ver
    2018
    Posts
    2

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to calculate hours worked w/lunch or w/o + OT column only total after 40 hours
    By blinhart in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-30-2018, 01:27 PM
  2. Replies: 4
    Last Post: 07-01-2017, 08:35 AM
  3. Replies: 1
    Last Post: 05-19-2017, 07:37 AM
  4. [SOLVED] Formula to calculate penalties for a race
    By Cyran in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-16-2016, 04:40 AM
  5. [SOLVED] Need formula to calculate meal penalties
    By Calc7827 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-18-2015, 10:40 AM
  6. Replies: 8
    Last Post: 06-11-2013, 05:34 PM
  7. Formula to calculate date and time stamps + lunch
    By excelgeezer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-02-2013, 05:35 AM

Bookmarks

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