Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Subtract start time from end time minus 30 minutes lunch break

  1. #1
    Join Date
    19th September 2012
    Posts
    73

    Subtract start time from end time minus 30 minutes lunch break

    I have an Excel spreadsheet with start time in column A and end time in column B. The formula I came up with displayed the time difference as “Negative dates or times are displayed as ##########. What this means is that the formula is not able to handle times past 12 midnight. I would like the formula to take into consideration time difference past midnight in order to fix the problem.

    Here is my formula:

    =IF(OR(ISBLANK(B3),ISBLANK(A3)),"",B3-A3-(B3-A3>TIME(8,0,0))*TIME(0,30,0))


    NOTE: The formula works fine for deducting the 30 minutes lunch break if someone works an eight (8) hour shift (for example, 8:00am to 4:30pm; where the 30 minutes is lunch break. So instead of 8:30 minutes, the formula works the way it should (i.e. show 8:00 hours). It also works fine for time difference such as 8:00pm minus 4:00pm = 4: 00. However, if the time difference is 8:30 pm minus 4:00 pm, the 30 minutes is part of the solution instead of displaying 4 hours. I want the formula for the time difference to fix this as well.
    I have attached a sample copy of the data.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th September 2010
    Posts
    127

    Re: Subtract start time from end time minus 30 minutes lunch break

    Try:
    =B3-A3+(A3>B3)-"0:30"
    Look here:
    http://www.myonlinetraininghub.com/e...ulation-tricks

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th September 2012
    Posts
    73

    Re: Subtract start time from end time minus 30 minutes lunch break

    Armando: the formula suggested did work but it displayed 3:30 instead of 4:00 hours for 3pm minus 11am instead of 4 hours. What I want is for the formula to subtract times such as 3pm minus 11am, 4pm minus 11am and so on. However, I want the formula to subtract 30 minutes from the time difference if it is 2:30pm minus 1am.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th September 2010
    Posts
    127

    Re: Subtract start time from end time minus 30 minutes lunch break

    Try:
    =IF((B3<A3)+B3-A3>8/24,(B3<A3)+B3-A3-0.5/24,(B3<A3)+B3-A3)
    Or:
    =IF(OR(A3="",B3=""),"",IF((B3<A3)+B3-A3>8/24,(B3<A3)+B3-A3-0.5/24,(B3<A3)+B3-A3))

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    2nd July 2012
    Posts
    36

    Re: Subtract start time from end time minus 30 minutes lunch break

    Hi

    Those hours in Col C are the y basic hours! Not overtime!

    Try this, it will calculate the hours after 8.5, also past midnight: =IF(COUNTA(A3:B3)<2,"",MAX(MOD(B3-A3,1)-(1/24*8.5),0))

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th September 2012
    Posts
    73

    Re: Subtract start time from end time minus 30 minutes lunch break

    Armando: Thank you so much for the assistance. The suggested formulas did the trick.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    2nd July 2012
    Posts
    36

    Re: Subtract start time from end time minus 30 minutes lunch break

    Glad you got it sorted, so all your staff are on overtime as soon as they turn up, perhaps I should apply for a job!!

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    18th September 2010
    Posts
    127

    Re: Subtract start time from end time minus 30 minutes lunch break

    Glad it worked and thanks for the feedback.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 3
    Last Post: September 27th, 2012, 00:18
  2. Replies: 9
    Last Post: October 16th, 2007, 16:53
  3. Replies: 3
    Last Post: October 15th, 2007, 16:08
  4. Replies: 3
    Last Post: September 14th, 2007, 07:48
  5. Replies: 11
    Last Post: May 8th, 2006, 12:48

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
  •  
porno