1. ## 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

2. ## Re: Help to adapt a formula (2003 SP2)

Hi Steve

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

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

4. ## 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. ## Re: Help to adapt a formula (2003 SP2)

Or to eliminate the IF:

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

Steve

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

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

8. ## 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. ## 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

10. ## Re: Help to adapt a formula (2003 SP2)

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

11. ## Re: Help to adapt a formula (2003 SP2)

Hi Hans

Hopefully this will shed some light on my problem

Cheers

Steve

12. ## 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. ## 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>

14. ## 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

15. ## 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 Last

#### Posting Permissions

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