On Jul 24, 11:35*am, "Bob Phillips" wrote:
Not really.
I think you are saying that they can accrue leave at the end of each year,
so if they takes 7 of 17 days, the 10 is carried over and with the 17 for
the new year, they now have 27. But *you seem to want more, but I am not
sure what.
--
__________________________________
HTH
Bob
"Tia" wrote in message
...
On Jul 24, 10:59 am, "Bob Phillips" wrote:
I am a tad confused by what you want to do.
You have the leave entitlement, what more is there to know? What is C8
calculating, and what is the relevance of this mysterious =5*15+(C8-5)*17
formula.
--
__________________________________
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 -
I have the formula to give me based on the starting date the total of
days pr year
what i cannot automaticly calculate is :
If you check the rules you will see the following
Every employee has the right to keep his vacations for 2 years so
whenever he finishes his 4 th year and and start his 5th all his
vacation will be as 17 days wich what should happened is 15 days from
the first year and 17 days for the second thats why i was using this
formula =5*15+(C8-5)*17 wich means the first 5 years*15 days pr year
+starting date-5 years that they are entitled in for 15 days as pr the
rules and the rest of the time *17 days
from 1 till 5 years = 15 days
from 5 till 10 years = 17 days
from 1o till 15 years = 19 days
I am using this formula to give me how many days allowed pr year as pr
today 15 or 17 or 19
what i need is a formula that deduct the yearswhenever i reach 5 years
or 10 or 15 years automaticly from the starting date
I hope i am being clear if i am not i can always send you my workbook
Plz help
Tia- Hide quoted text -
- Show quoted text -
Yes exactly
But the issue is that the formula 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()-C710*365,19,IF(TODAY()-
C75*365,17,IF(TODAY()-C70.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
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