+ Reply to Thread
Results 1 to 14 of 14

Finding duration working hour

  1. #1
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Borneo
    MS-Off Ver
    2016
    Posts
    277

    Finding duration working hour

    Dear Expert

    Need your help to find formula for column G
    if starting 07:30 till 10:00 no break time, then duration only finish time - start time
    but if starting time till finish time there's a break time inside, duration time should be decrease 1 hour

    table for working our in sheet 2
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Finding duration working hour

    Please Login or Register  to view this content.
    Try this formula
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  3. #3
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Borneo
    MS-Off Ver
    2016
    Posts
    277

    Re: Finding duration working hour

    dear willem

    not working

    the sampe in G3 and G4 is the result expected

  4. #4
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Finding duration working hour

    This formula just calculate if there is a breaking time.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-07-2019
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    22

    Re: Finding duration working hour

    Try this...

    =(F3-E3)*24-IF((F3-E3)*24>22,1,IF((F3-E3)*24>5,0.5,0)) Paste it into the cell H3. Then "CTRL+SHIFT+ENTER" to make it work. Where I have 0.5 at the end is to allow a half hour break if they work less than 5 hours. You can change it to 0. should work. I use this in my rota hours calculator for my warehouse staff.

  6. #6
    Registered User
    Join Date
    10-07-2019
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    22

    Re: Finding duration working hour

    as a little sidenote, means you can delete unnecessary columns and make the sheet simpler..... Here is the sheet I use for work. I have a calculator for hours worked in a day by all staff plus hours total for each person per week and total hours worked per week included in it. I'll even let you copy it if you want. :-) I've also got conditional formatting to highlight the weekly hours per staff member in bright red if they are scheduled to work more than 40 hours over the week - my firm don't like paying overtime!!!
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Borneo
    MS-Off Ver
    2016
    Posts
    277

    Re: Finding duration working hour

    Dear All

    from all ideas above, formula from Willem almost correct, but it will not working in long shift

    any idea???

  8. #8
    Registered User
    Join Date
    10-07-2019
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    22

    Re: Finding duration working hour

    I've added in the formula. Is there a length of shift that warrants a break or do you want to be able to add it in manually? My formula only works if its automatically deducted, so if after working a 5 hour shift the person gets a 1 hour break. To do that, you just need to fiddle with the formula a little
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Borneo
    MS-Off Ver
    2016
    Posts
    277

    Re: Finding duration working hour

    Dear Mat

    If I change E4 with 19:30 PM then F4 with 07:30 AM, during this time there's a break time for an hour. formula doesn't working

  10. #10
    Registered User
    Join Date
    10-07-2019
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    22

    Re: Finding duration working hour

    I see. Sorry, you're right. This wont work for an overnight shift. I'll have to have a quick play around...

  11. #11
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Borneo
    MS-Off Ver
    2016
    Posts
    277

    Re: Finding duration working hour

    any solution?

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Finding duration working hour

    Perhaps this will help.
    On Sheet2 column G is populated using: =F1-E1
    On sheet1 column I is populated using: =IFERROR(INDEX(Sheet2!A$1:A$4,AGGREGATE(15,6,ROW(I$1:I$4)/(Sheet2!B$1:B$4=E3)/(Sheet2!C$1:C$4=F3),1)),"")
    The Duration column is populated using: =MOD(F3-E3,1)-IFERROR(INDEX(Sheet2!G$1:G$4,MATCH(I3,Sheet2!$A$1:$A$4,0)),0)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  13. #13
    Registered User
    Join Date
    10-07-2019
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    22

    Re: Finding duration working hour

    I figured it out..... If you put the finish time overnight as...and hear me out on this.... 28:00 for a 4am finish, it will add up the hours and introduce the break PLUS it will then change your time entry to 04:00 for you so the record looks normal. The formula will have to be played with to make the automatic break calculator work for the length of time they have to work before being given a break.

  14. #14
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Finding duration working hour

    I'm not sure did this file is suit to your requirement.
    This file was a modification from this post
    https://www.excelforum.com/excel-for...ml#post5236403

    By adding break time(s).
    But from logic of the file, start/stop time must not any part of break time(s) or else it may mis calculation.
    (in case break time cross midnight you may split it into 2 break time) and please not enter 00:00 or 24:00
    because it may lead to mis calculation too (change to 23:59:59 or 00:00:01).

    Hope it's usefull.


    Regards.
    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. break time duration into individual hour blocks
    By chriswrcg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-29-2019, 04:23 PM
  2. Removing lunch hour from Log Sheet. (Removing Specific Hour duration)
    By SirTypos in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-31-2018, 11:01 PM
  3. [SOLVED] Formula to convert 24 hour day to 8 hour (working) day?
    By miro2021 in forum Excel General
    Replies: 4
    Last Post: 07-31-2017, 10:06 AM
  4. Replies: 4
    Last Post: 10-02-2015, 10:00 AM
  5. Calculate employees working from hour to hour
    By otter in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-03-2014, 08:57 AM
  6. Bar Stock Graph 24 Hour Duration
    By thermometer in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-18-2014, 08:39 PM
  7. [SOLVED] Working out Calls Per Hour. 8 Hour Day.
    By sturmy in forum Excel General
    Replies: 3
    Last Post: 03-24-2014, 11:05 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