+ Reply to Thread
Results 1 to 6 of 6

delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

  1. #1
    Registered User
    Join Date
    07-22-2016
    Location
    North America
    MS-Off Ver
    office 2010 Education version
    Posts
    2

    Exclamation delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

    i'm sure this has been asked more than once before but i've spent hours wading thru thousands of posts and search hasn't been any more helpful so thanks for understanding

    what i need is a formula that will take the sum of start and end times then check if it is greater than 6 hours and if true will deduct a half hour from hours worked.
    i also need it to display 0 hours worked if anything other than a start time is entered, like off, or paid day off etc

    thanks very much in advance

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

    Try

    =IFERROR((B2-A2)-((B2-A2>="6:00"+0)*"0:30"),0)

    A2 = Start Time
    B2 = End Time

  3. #3
    Registered User
    Join Date
    07-22-2016
    Location
    North America
    MS-Off Ver
    office 2010 Education version
    Posts
    2

    Re: delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

    thanks for the help!

    this works beautifully

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

    You're welcome.

    AFter some thought, may need to adjust if you have TimeFrames that span midnight.
    Like if Start was 20:00 and End was 4:00

    Try this instead
    =IF(COUNT(A2:B2)=2,MOD(B2-A2,1)-((MOD(B2-A2,1)>="6:00"+0)*"0:30"),0)
    Last edited by Jonmo1; 07-22-2016 at 11:05 AM.

  5. #5
    Registered User
    Join Date
    07-28-2016
    Location
    Tville
    MS-Off Ver
    2010
    Posts
    1

    Re: delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

    This is my first post so I hope I am doing this right lol!

    I am having a simular issue with deducting lunch breaks from daily in/out times. Due to business some days we take 1/2 hour lunches or 1 hour lunches when 5 or more hours worked. If an employee works less than 5 hours then no lunch is given and the time should not be deducted.

    I attached a sample of the spreadsheet I am working on with the two formulas, and I am trying to figure out but with no luck. I have added comments to help explain my situation with both formulas.

    Thank you
    Attached Files Attached Files

  6. #6
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    488

    Re: delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

    Hi Wayoutman,

    You really should start your own thread

    I have however restructured your example

    so please next time keep to the forum rules
    Attached Files Attached Files

+ 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. Replies: 7
    Last Post: 04-25-2020, 03:23 AM
  2. [SOLVED] Formula to automatically adjust lunch hour based on start time
    By Zyphon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-26-2015, 07:23 AM
  3. time calculation without lunch hour
    By Harrold in forum Excel General
    Replies: 7
    Last Post: 01-29-2012, 02:49 AM
  4. [SOLVED] Adding half an hour to a time value
    By DaZombie in forum Excel General
    Replies: 7
    Last Post: 10-25-2010, 11:41 AM
  5. Rounding Time up to the nearest half hour
    By levivm in forum Excel General
    Replies: 3
    Last Post: 09-21-2007, 01:20 AM
  6. [SOLVED] how to calculate time start & time finish in quarter hour
    By Peter Wu in forum Excel General
    Replies: 3
    Last Post: 06-06-2006, 08:10 PM
  7. Calculate time difference to the half hour
    By Ken Ivins in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 03:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1