Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help to adapt a formula (2003 SP2)

    Good afternoon

    I have a formula =(G6-H6)*24*60/60 which determines the amount of overtime my drivers earn if they work above and beyond 9 hours, if they work any hours on a Saturday or Sunday they get paid a minimum of 4 hours, how can I reflect that in the formula =(G6-H6)*24*60/60,IF(Weekday>5,Minimum 4 or something to that effect

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help to adapt a formula (2003 SP2)

    Hi Steve

    How about in J1:

    =IF(AND(WEEKDAY(F1)>5,I1<4),4,(G1-H1)*24*60/60)
    Jerry

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help to adapt a formula (2003 SP2)

    Hi Steve,

    I'm taking a shot in the dark, but see if this is close to what you need??...

    =IF(AND((G6-H6)*24*60/60<4,WEEKDAY('Replace with cell ref containing weekNum')>5),4,(G6-H6)*24*60/60)
    or
    =IF(AND((G6-H6)*24*60/60<4,WEEKDAY('Replace with cell ref containing weekNum')>5),4+(G6-H6)*24*60/60,(G6-H6)*24*60/60)
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to adapt a formula (2003 SP2)

    You can omit *60/60 from your formula, multiplying by 60 then dividing by 60 leaves the number unchanged.

    Assuming that F6 contains the date, you could use this formula:
    <code>
    =MAX((G6-H6)*24,IF(WEEKDAY(F6,2)>5,4,0))
    </code>
    Replace F6 with the appropriate reference.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help to adapt a formula (2003 SP2)

    Or to eliminate the IF:

    =MAX((G6-H6)*24,(WEEKDAY(F6,2)>5)*4)

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to adapt a formula (2003 SP2)

    Thank you all as usual for you responses

    The nearest one that seems to work at the moment seems to be the one provided by Hans, however it gives the result of 4 irrespective of whether or not the driver worked perhaps this screenshot will help elaborate a little, if the driver works 5 minutes on a Saturday or Sunday I would like column I to show 4 as a minimum but if they do not work at all it should be blank

    Cheers

    Steve
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to adapt a formula (2003 SP2)

    Sorry Steve

    You posted whilst I was composing, your suggestion works the same as it always inserts 4 for the weekends irrespective of whether the driver worked or not

    Thanks for you help though

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to adapt a formula (2003 SP2)

    You can add an IF to check whether the driver has worked at all:
    <code>
    =IF(G6="","",MAX((G6-H6)*24,(WEEKDAY(D6,2)>5)*4))
    </code>
    Adapt as desired (you can test another cell than G6, for example)

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to adapt a formula (2003 SP2)

    Hi Hans

    I have copied and pasted your formula and dragged it through the relevant cells but it still shows 4 on the weekend dates whether or not G6-12 have any values in them, any idea what I am doing wrong?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to adapt a formula (2003 SP2)

    Could you attach a copy of your worksheet with some dummy data?

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to adapt a formula (2003 SP2)

    Hi Hans

    Hopefully this will shed some light on my problem

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to adapt a formula (2003 SP2)

    You're hiding zero values (the Zero Values check box in the View tab of Tools | Options has been cleared), so although G10 appears to be empty, but it actually contains 0.
    Here are two possible workarounds for I6 (can be filled down):

    1) Test whether G6 is 0:
    <code>
    =IF(G6=0,"",MAX((G6-H6)*24,(WEEKDAY(D6,2)>5)*4))
    </code>
    2) Test whether E6 is blank:
    <code>
    =IF(E6="","",MAX((G6-H6)*24,(WEEKDAY(D6,2)>5)*4))
    </code>
    HTH

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to adapt a formula (2003 SP2)

    The "blank" cells in column G are not blank, they contain formulas. Try this formula in I6 and fill down.

    <code>
    =IF(OR(E6="",F6=""),"",MAX((G6-H6)*24,(WEEKDAY(D6,2)>5)*4))
    </code>
    Legare Coleman

  14. #14
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to adapt a formula (2003 SP2)

    Thank you both Hans and Legare

    Both suggestions worked fine but in doing so has created another small problem, there are formulas in I13, L13 and N13 which determines weekday O/T, Saturday O/T and Sunday O/T but they are now returning a #Value error (the font colour is set to white so that they are not visible when I have the worksheet locked, the formula for the weekday O/T is =SUMPRODUCT((WEEKDAY(D612,2)<6)*I6:I12)

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to adapt a formula (2003 SP2)

    You could return 0 instead of "". Since you're hiding zeros, you won't see the difference, but the SUMPRODUCT formula can handle the zeros.
    <code>
    =IF(E6="",0,MAX((G6-H6)*24,(WEEKDAY(D6,2)>5)*4))
    </code>
    and similar for the alternative versions.

Page 1 of 2 12 LastLast

Posting Permissions

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