Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Computing Overtime: A Longstanding Question

  1. #1
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Eons ago Hysen posted at the old board (see http://www.mrexcel.com/wwwboard/messages/7738.html ) the following question:

    QUOTE
    Posted by Hysen on December 01, 2001 at 04:15:56:

    I have set up a Timesheet, which calculates the no. of total hrs worked between start time and end time. The work hours can go over midnight. E.g. start at 22:00 and end at 5:00 the next morning.

    My question is: In addition to working out the total hrs worked, which I have done, I also need to calculate the number of hours & minutes (out of total hrs worked) that a employee works between 22:00 and 6:00. There are many scenarios, too many for using an if statement. Egs are;

    start@ end@ Hrs between 22:00 & 6:00 worked
    18:00 2:00 4 hrs
    23:00 7:00 7 hrs
    14:00 22:30 30 mins.
    Is there a formula I could use or do I have to resort to a macro.
    Thanking in advance.

    Hysen.

    UNQUOTE

    My initial approach was top-down, which I had to abandon very quickly. I published at least 2 early (insufficient) versions of the ongoing struggle as a response to similar questions at a news group. The final formula that eventually appears to resolve the problem is the result of countless bottom-up efforts (whence many IFs!). It seems appropriate that I post it at this board where all this has started:

    =IF(OR(AND(A2 <= eCRIT,B2 >= sCRIT),AND(A2 <= eCRIT,B2 < eCRIT,B2-A2 < 0),A2 = B2),
    MOD(eCRIT-A2,1)+MOD(B2-sCRIT,1),
    IF(AND(OR(A2*24 < 12,NOT(B2-sCRIT > 0),B2*24 >= 12),NOT(AND(B2-A2 < 0,A2*24 > 12,A2 < sCRIT,B2 > eCRIT)),A2 < sCRIT,B2 > eCRIT,A2*24 <> 0,eCRIT < A2,B2 < sCRIT,NOT(AND(A2*24 < 12,B2*24 < 12,A2 >= eCRIT,B2-A2 < 0))),
    0,
    MOD(IF(B2 <= eCRIT,B2,IF(B2 >= sCRIT,B2,eCRIT))-IF(A2 <= eCRIT,A2,IF(A2 <= sCRIT,sCRIT,A2)),1)))


    where sCRIT and eCRIT defines the interval for overtime (e.g., 22:00 and 6:00 respectively, as in Hysen's case). Many thanks to Hysen Struga and Jon Crash who helped me to finalize it.

    If anyone wants to devise an alternative or to shorten the one above, I can send a test set to experiment with.

    Special Note. Ian: I think the peculiar problem that you noted wrt MOD in computing time differences seems to be not a problem here.

    PS. It's a hard job to post lengthy formulas!

    Additional Note (added on: Sun, Feb 17, 02). The formula was crippled because of the existence of a number of the less than signs (a troubling problem at the old board which appears to be still with us). If interested in the formula, just drop me a line at

    akyurek@xs4all.nl





    [ This Message was edited by: Aladin Akyurek on 2002-02-16 16:01 ]

    [ This Message was edited by: Aladin Akyurek on 2002-02-16 16:10 ]

    [ This Message was edited by: Aladin Akyurek on 2002-02-17 07:23 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The secret with time is to get the formatting right.
    I am not sure I understand you plight but in order to get the correct evaluationof elapsed time you need :
    5 cols
    a start time
    b

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The secret with time is to get the formatting right.
    I am not sure I understand you plight but in order to get the correct evaluation of elapsed time you need :
    5 columns
    a start time Format (custom hh:mm)
    b finish time Format (custom hh:mm)
    c formula to establish diff in decimal form = (B1-A1)+If((A1>B1,1) format general
    d conversion to 24hr formula = (B1-A1)+If(A1>B1,1) Format time 13:30
    e diff in hours ( * 60 in minutes reqd)formula =If(c1=0,24,D1*24) format general

    If you then hide cols c and d you can then summate all column e ( e being the shift total in hours or minutes).
    This I think would simplify your analysys.
    kk


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Further to my previous answer you can change column f to this and this will return zero if there is no start and finish times.

    If you change the cell F1 to
    =IF(AND(A1=0,B1=0,TRUE),0,IF(C!=0,24,D1*24))
    This will test for nil entry in both start and finish times

    This is because in our business we work 365 days and you could have the same start and finish time which would mean a day has expired
    Hope this helps
    kk

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Bravo. I started playing with a "simplified" boolean formula, but quickly dismissed it when head just about exploded thinking of the different options. At the end of day, things would be greatly simplified if a true time and date were placed in this rather than "tricking" excel into beleiveing it was the next day.

  6. #6
    Guest

    Default

    I have tried doing timecards with only cell formulas, the place had some flakey policies, such as allowing workers to clock in as soon as they walked in the door, even though the shift started an hour later, the forklift guy got a fifteen minute early start, the supervisor was paid from the moment he clocked in.............


    You need to click Alt F11 and discover the Visual Basic Applications Editor.

    When you are done, you will:

    Have some (forms toolbox) buttons on the sheet.

    Be well acquainted with "Cells(row,col).value" notations.

    Become good with For loop = start to finish...Next control structures.

    I tell you, I am embarrassed to admit how hard I beat the dead horse of cell formulas before I decided to tap Alt F11 and discover VBA

    Good Luck!

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-17 20:09, IML wrote:
    Bravo. I started playing with a "simplified" boolean formula, but quickly dismissed it when head just about exploded thinking of the different options. At the end of day, things would be greatly simplified if a true time and date were placed in this rather than "tricking" excel into beleiveing it was the next day.
    Ian: Maybe you're thinking of registering the date as well as the time when entering start and end times. This has come up at the Microsoft news group for worksheet functions, giving me the hope that I could drop further effort devising a single formula. But, alas. I don't know why that didn't work. Here is the thread where that has been proposed:

    http://groups.google.com/groups?hl=e...4ax.com#link10

    Another relevant thread is:

    http://groups.google.com/groups?q=ca...=Google+Search

    Both threads contain refs to the earlier versions of the formula I posted here.

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •