an error in a formula in annual leave
 

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(J63600,(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

an error in a formula in annual leave
 

On Tue, 19 Aug 2008 02:54:02 -0700 (PDT), Tia wrote:

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(J63600,(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