On Jul 24, 2:16*pm, "Bob Phillips" wrote:
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- Hide quoted text -
- Show quoted text -
When you have 15 years of service you should get as a leave
15*5 for the first 5 years
17*5 for the 2nd 5 years
19*5 for the 3rd 5 years
so i used this formula cause the first 5 years he should have 15 days
only the second five he should get 17
im really to sorry to bother but the total should be 80.27
S/d is C7=05/01/2003 C8==+(NOW()-C7)/30/12=5.31
cause =5*15+(C8-5)*17 __________=5*15+(5.31-5)*17=80.27
But when i use your formula i get
=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)= 92