T

#### Tia

I am trying to do annual leave calculation what i need is an

automatic

calculation for the following

The rule is

Every employee is entitled to a fully paid annual vacation after the

completion of 3 months

1 to 5 years of consecutive service fifteen (15) days per year

5 to 10 years of consecutive service seventeen (17) days per year

10 to 15 years of consecutive service nineteen (19) days per year

Each employee has the right to keep his vacation for 2 years so the

problem occurs when the first left vacation is the 4 th and the 5 th b

it calculate total remaining of the 4th and the 5th *17 not 15 days

from the first year and 17 days from the 5 th year

The issue is that the formula that i have is calculating entitled

years wich

is 15 or 17 or 19 * starting date what i want is to calculate 15 days

for the first 5 years and 17 days for the 2snd 5 years

example :

B4= Name C4= x

B5=Staff Number C5=500

B6= Position C6=hr

B7= Starting Date C7=5/1/2003

B8= Total Working Days C8= 5.31 YEARS =+(NOW()-C7)/30/12

B9= Annual Leave

B10=TOTAL ENTITLED LEAVE =IF(TODAY()-C7>10*365,19,IF(TODAY()-

C7>5*365,17,IF(TODAY()-C7>0.25*365,15)))=17

B11=TOTAL DAYS TILL THIS DAY ????????????????? I usually use the

following formula

=+C8*B10 if i use it i will have this total days =90.26 but whenever

the employee reaches the 5 th years the formula will calculate wrong b

it takes the toltal days pr year only without taking into

conmsideration that difference between years

so i manually enter the following formula =5*15+(C8-5)*17 =80.27

what i need is an automatic formula that deduct them automaticly

without manually entering the formula when the employee reached his

5th,1o th or 15 th years of services

I hope that i made what i want clear this time

Tia