Actually that can be a lot simpler
=IF((TODAY()-C7)365/4,
(ROUNDUP((TODAY()-C7)/365,0)*15)
+((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2)
+((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-10))*2),0)
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Bob Phillips" wrote in message
...
Correction
=IF((TODAY()-C7)365/4,
((ROUNDUP((TODAY()-C7)/365,0)0)*15*ROUNDUP((TODAY()-C7)/365,0))
+((ROUNDUP((TODAY()-C7)/365,0)5)*(ROUNDUP((TODAY()-C7)/365,0)-5)*2)
+((ROUNDUP((TODAY()-C7)/365,0)10)*(ROUNDUP((TODAY()-C7)/365,0)-10)*2),0)
--
__________________________________
HTH
Bob
"Tia" wrote in message
...
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
B
C
D
4 Name George
5 Staff Number 50004
6 Position
7 Starting Date 01-May-03
8 Total Working Days (NOW()-C7)/30/12
9 Annual Leave Days Taken Days
Remaining
10 15
+D39 B11- C10
11 Total Days Allowed
12 =5*15+(C8-5)*17
39 From Day Inclusive To Day Exclusive Total
Annual Leave
A8 =The formula that i am using to find the Annual Leave pr year is
the following
=IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()-
C70.25*365,15)))
What i want is an automatic calculation for the total days allowed
instead of manually putting the formula i want it to deduct the
previous 5 years as pr the rules =5*15+(C8-5)*17
What is the formula that allows me to do that ?
what i need is an automatic formula to be updated alone on the B12
Thx in advance