Announcement

Collapse
No announcement yet.

Subtract start time from end time minus 30 minutes lunch break

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      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.

      Comment


      • #4
        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))

        Comment


        • #5
          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))

          Comment


          • #6
            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.

            Comment


            • #7
              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!!

              Comment


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

                Glad it worked and thanks for the feedback.

                Comment

                Working...
                X