+ Reply to Thread
Results 1 to 11 of 11

Lunch Time Dilemma

  1. 08-14-2021, 05:38 PM #1
    RYAN888
    RYAN888 is offline
    Registered User
    Join Date
    08-14-2021
    Location
    LONDON
    MS-Off Ver
    2019
    Posts
    6

    Question Lunch Time Dilemma

    Hi everyone,

    I am doing rotas for my company and I need to deduct 30mins if an employee has worked less than 8hrs and deduct 1hr if more than 8hrs.
    Below is a formula that I found from this forum and I have lost the thread now, the formula works really well however if somebody is off on Monday, his/her hour shows as -0.5 and it is affecting the overall worked hours at the end of the week.

    The Formula
    =(I9-H9)*24-IF((I9-H9)*24>8,1,IF((I9-H9)*24<9,0.5)),


    I need the daily total worked hours to show as 0 if the person didn't work on that day.

    I hope it makes sense.

    Thank you!
    Attached Files Attached Files
    Last edited by RYAN888; 08-14-2021 at 06:16 PM. Reason: Adding Attachment
    Reply With QuoteRegister To Reply
  2. 08-14-2021, 05:41 PM #2
    mikeava
    mikeava is offline
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: Lunch Time Dilemma

    Hi - can you attach your file? Formula doesn't do much good without seeing the entire picture.

    Thanks
    Reply With QuoteRegister To Reply
  3. 08-14-2021, 06:17 PM #3
    RYAN888
    RYAN888 is offline
    Registered User
    Join Date
    08-14-2021
    Location
    LONDON
    MS-Off Ver
    2019
    Posts
    6

    Re: Lunch Time Dilemma

    Hi,

    I have added the file in my original post. Thank you
    Reply With QuoteRegister To Reply
  4. 08-14-2021, 08:44 PM #4
    mikeava
    mikeava is offline
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: Lunch Time Dilemma

    How about
    =IF(B9=0,0,(C9-B9)*24-IF((C9-B9)*24>8,1,IF((C9-B9)*24<9,0.5)))

    So if Monday start time (b9) is 0 then put 0 otherwise use your calculation

    Let me know if this works for you
    Reply With QuoteRegister To Reply
  5. 08-15-2021, 02:39 AM #5
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Lunch Time Dilemma

    In D9 copied to other ranges

    =((C9-B9)-IF((C9-B9)=0,0,IF((C9-B9)<=TIMEVALUE("8:00"),"0:30","1:00")))*24

    Lot of merged cells are there. Two rows are merged. It is always better not to use merged cells as for as possible. In the present file it appears merged cells are not required. Try to avoid.
    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.
    Reply With QuoteRegister To Reply
  6. 08-15-2021, 03:07 AM #6
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,060

    Re: Lunch Time Dilemma

    Just add MAX at the front part of your formula, cell D9
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 08-15-2021, 07:52 AM #7
    RYAN888
    RYAN888 is offline
    Registered User
    Join Date
    08-14-2021
    Location
    LONDON
    MS-Off Ver
    2019
    Posts
    6

    Re: Lunch Time Dilemma

    This has worked beautifully. Thank you so much for everybody's contribution.
    Reply With QuoteRegister To Reply
  8. 08-15-2021, 07:53 AM #8
    RYAN888
    RYAN888 is offline
    Registered User
    Join Date
    08-14-2021
    Location
    LONDON
    MS-Off Ver
    2019
    Posts
    6

    Re: Lunch Time Dilemma

    Quote Originally Posted by josephteh View Post
    Just add MAX at the front part of your formula, cell D9
    Please Login or Register  to view this content.
    This has worked beautifully. Thank you so much for everybody's contribution.
    Reply With QuoteRegister To Reply
  9. 08-15-2021, 07:57 AM #9
    RYAN888
    RYAN888 is offline
    Registered User
    Join Date
    08-14-2021
    Location
    LONDON
    MS-Off Ver
    2019
    Posts
    6

    Re: Lunch Time Dilemma

    Quote Originally Posted by josephteh View Post
    Just add MAX at the front part of your formula, cell D9
    Please Login or Register  to view this content.
    May I ask, what was the thought process to figure this out?
    Reply With QuoteRegister To Reply
  10. 08-15-2021, 08:35 AM #10
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,060

    Re: Lunch Time Dilemma

    That's what I normally do to set a minimum threshold, and use MIN for a maximum threshold.
    Reply With QuoteRegister To Reply
  11. 08-15-2021, 03:12 PM #11
    RYAN888
    RYAN888 is offline
    Registered User
    Join Date
    08-14-2021
    Location
    LONDON
    MS-Off Ver
    2019
    Posts
    6

    Re: Lunch Time Dilemma

    Quote Originally Posted by josephteh View Post
    That's what I normally do to set a minimum threshold, and use MIN for a maximum threshold.
    Interesting, thank you very much.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to calculate time worked: Start time, end time, lunch break
    By khalinguyen191999 in forum Excel General
    Replies: 1
    Last Post: 09-14-2020, 11:37 PM
  2. How to calculate time worked: Start time, end time, lunch break
    By welchs101 in forum Excel General
    Replies: 7
    Last Post: 04-25-2020, 03:23 AM
  3. Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break
    By NiecieD in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2018, 03:52 PM
  4. delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time
    By D S in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-29-2016, 03:15 AM
  5. [SOLVED] calculate time in and time out minus lunch hours
    By rjassal82 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-29-2013, 10:43 AM
  6. [SOLVED] Needing to combine 2 IF statements in one Cell. Time Schedule minus lunch time
    By cody_o in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2013, 04:10 PM
  7. Adding up Absent time taking lunch time into consideration
    By abrilabs in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2013, 02:02 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1