Hi
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