an error in a formula in annual leave

[Tia]

Hi
I am working on a formula that allows me to know how many days the
employee has,i have found a formula but i still have one with error
the rules are the following:
3 months till 4.99 years =15 year
5 years till 9.99= 17 days pr year
10 years till 14.99= 19 days pr year
The table is like the following
L M N
2 3----4.99 5----9.99 10----14.99

L3=IF((J6)<1800,I6*15,0)
M3=IF(1800<J6<3600,(1800)*15/12/30+(J6-1800)*17/12/30,0)
N3 =IF(J6>3600,(1800)*15/360+(1800)*17/12/30+
(J6-1800-1800)*K6/12/30,0)
I
J K
5 Total Working Years Total Working Days Total Days Allowed /Year
6 5.33
1918.00 7

The M3 Formula is not working properly what have i done wrong

Please help

Tia

 

[Ron Rosenfeld]

Hi
I am working on a formula that allows me to know how many days the
employee has,i have found a formula but i still have one with error
the rules are the following:
3 months till 4.99 years =15 year
5 years till 9.99= 17 days pr year
10 years till 14.99= 19 days pr year
The table is like the following
L M N
2 3----4.99 5----9.99 10----14.99

L3=IF((J6)<1800,I6*15,0)
M3=IF(1800<J6<3600,(1800)*15/12/30+(J6-1800)*17/12/30,0)
N3 =IF(J6>3600,(1800)*15/360+(1800)*17/12/30+
(J6-1800-1800)*K6/12/30,0)
I
J K
5 Total Working Years Total Working Days Total Days Allowed /Year
6 5.33
1918.00 7

The M3 Formula is not working properly what have i done wrong

Please help

Tia

=IF(AND(1800<J6,J6<3600),(1800)*15/12/30+(J6-1800)*17/12/30,0)

But you might find it simpler, depending on the rules for determining working
years, to just set up a lookup table, and compute the years worked using the
DATEDIF function. This is built-in to Excel but documented at
http://www.cpearson.com/excel/datedif.aspx



--ron