Excel 2000, IF function with multiple condition

[HowHow]

I am using Excel 2000 to calculate the pay for worker and there are 4 possibility:
1.If actual work time is within 5 minutes under rostered time, pay rostered time
2.If actual work time is 5 minutes more under rostered time, pay actual work time
3.If actual work time is within 5 minutes over rostered time, pay actual work time
4.If actual work time is 5 minutes more than rostered time, pay rostered time plus 5 minutes.

For example:
Case1:
Rostered Time: 1.50 (1 hour 30 minutes convert to decimal =1.50)
Actual work time: 1.47 ( 1 hour 28 minutes)
Pay: 1.50

Case2:
Rostered Time: 1.50
Actual Work time: 1.37 (1 hour 22 minutes)
Pay:1.37

Case3:
Rostered Time: 1.50
Actual Work Time: 1.56 (1 hour 34 minutes)
Pay: 1.56

Case4:
Rostered Time:1.50
Actual Work Time: 1.60 (1 hour 37 minutes)
Pay: 1.58


Note that the rostered hour is not always 1.5. It can be 1, or 2, or 0.25 or 0.50, or 0.75 depend on different service.

The formula below only works when the actual work time is 5 minutes over than rostered time:
K2=Actual Work time, L2=Rostered Time
=IF($K2-$L2>=0.08,$L2+0.08,IF($L2-$K2<=0.08,$L2,$K2))


The formula below won't pay up to rostered time if the actual work less than rostered time:
=IF((K59-L59<=0.08),K59,IF((K59-L59>0.08),L59+0.08,IF((L59-K59<=0.08),L59,IF((L59-K59>0.08),K59,""))))

Can anyone correct the formula for me please? or suggest a different formula altogether? Appreciate it.

[asedt]

=IF((K59-L59<=0.08),K59,IF((K59-L59>0.08),L59+0.08,IF((L59-K59<=0.08),L59,IF((L59-K59>0.08),K59,""))))

Do people really write things like that.. (http://personal-computer-tutor.com/if2.htm ho shit maybe the do) you can at least add some indentation so it's readable. Use if-else /select case instead?

=IF((K59-L59<=0.08),
    K59,
 
    IF((K59-L59>0.08),
        L59+0.08,
 
        IF((L59-K59<=0.08),
            L59,
 
            IF((L59-K59>0.08),
                K59,
                ""))))
 
 

My code:

if AW > RT then
     if AW > RT + 0.08 then
          RT + 0.08
     else
          AW
     end if
else
     if AW > RT - 0.08 then
          RT
     else
          AW
     end if
end if
 

I hope you can run that and that it works like you whished.

[HowHow]

Thanks for the link, I've got it:

Consider L is Rostered Time and K is Actual Work.

Case 1:
Paying rostered time if actual work is within 5 minutes under roster.
Paying actual working time if actual work is more than 5 minutes under roster. Paying up to rostered time if actual work is more than roster:

=IF($K2-$L2>=0,$L2,IF($L2-$K2<=0.08,$L2,$K2))

Case 2:
Paying rostered time if actual work is within 5 minutes under roster.
Paying actual working time if actual work is more than 5 minutes under roster.
Paying up to 3 minutes if actual work is more than rostered time:

=IF((AND($L2-$K2<0,$L2-$K2>=-0.05)),$K2,IF((AND($L2-$K2>=0,$L2-$K2<=0.08)),$L2,IF(($L2-$K2<-0.05),$L2+0.05,IF(($L2-$K2>0.08),$K2,""))))

Case 3:
All the same as above but paying up to 5 minutes if actual work is over the rostered time:

=IF((AND($L2-$K2<0,$L2-$K2>=-0.08)),$K2,IF((AND($L2-$K2>=0,$L2-$K2<=0.08)),$L2,IF(($L2-$K2<-0.08),$L2+0.08,IF(($L2-$K2>0.08),$K2,""))))

[asedt]

Case 3:
All the same as above but paying up to 5 minutes if actual work is over the rostered time:

=IF((AND($L2-$K2<0,$L2-$K2>=-0.08)),$K2,IF((AND($L2-$K2>=0,$L2-$K2<=0.08)),$L2,IF(($L2-$K2<-0.08),$L2+0.08,IF(($L2-$K2>0.08),$K2,""))))

Did you not get the last part:

A = AW, B = RT, 2 = limit ower and under RT

My code once agen:

=IF((A2>B2);IF((A2>B2+2);B2+2;A2);IF((A2>B2-2);B2;A2))
 

Gives:

AW    RT    
21,0    25    21
21,5    25    21,5
22,0    25    22
22,5    25    22,5
23,0    25    23
23,5    25    25
24,0    25    25
24,5    25    25
25,0    25    25
25,5    25    25,5
26,0    25    26
26,5    25    26,5
27,0    25    27
27,5    25    27
28,0    25    27
28,5    25    27
 

In Excel 2003, but 2000 can't be that different.