Annual Leave automatic calculation
 

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
1 Name Marcel Fouad Breidy
2 Staff Number 50005
3 Position ST1
4 Starting Date 01-May-03
5 Total Working Days =+(NOW()-C7)/30/12 =5.31 years

D
7 Annual Leave pr year Days Taken Days Remaining
8 17 61=D11
19.67=A10-D11
9 Total Days allowed
10 =5*15+(C8-5)*17
11 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 mannually 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 ?

Thank you in advance

Tia

Annual Leave automatic calculation
 

Tia,
This is incredibly hard to follow.
You have numbers 1 thru 5 and 7 thru 11. and Columns B,C,D??? What are
they? Obviously not row #s and column because your formulas don't tie.
Example: Cell C4 Starting Date is really C7 when looking at your formulas,
etc, etc.
Also your rules say nothing about deducting the previous 5 years but you
give an unidentified formula at the end of your post of =5*15+(C8-5)*17
Please clean it up and re-post.
We really DO want to help, but you've made it a bit difficult on this one.
Sincerely,
Gary Brown


"Tia" wrote:

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
1 Name Marcel Fouad Breidy
2 Staff Number 50005
3 Position ST1
4 Starting Date 01-May-03
5 Total Working Days =+(NOW()-C7)/30/12 =5.31 years

D
7 Annual Leave pr year Days Taken Days Remaining
8 17 61=D11
19.67=A10-D11
9 Total Days allowed
10 =5*15+(C8-5)*17
11 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 mannually 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 ?

Thank you in advance

Tia

Annual Leave automatic calculation
 

On Jul 23, 6:18*pm, Gary Brown
wrote:
Tia,
* This is incredibly hard to follow.
You have numbers 1 thru 5 *and 7 thru 11. and Columns B,C,D??? *What are
they? *Obviously not row #s and column because your formulas don't tie. *
Example: *Cell C4 Starting Date is really C7 when looking at your formulas,
etc, etc.
Also your rules say nothing about deducting the previous 5 years but you
give an unidentified formula at the end of your post of =5*15+(C8-5)*17
Please clean it up and re-post.
We really DO want to help, but you've made it a bit difficult on this one..
Sincerely,
Gary Brown



"Tia" wrote:
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
1 *Name * * * * * * * * * * * * * * * * * * * Marcel Fouad Breidy
2 *Staff Number * * * * * * * * * * * 50005
3 *Position * * * * * * * * * * * * * * * * * * * * * ST1
4 *Starting Date * * * * * * * * * * *01-May-03
5 *Total Working Days * * * * *=+(NOW()-C7)/30/12 =5.31 years

* * * * * * *D
7 *Annual Leave pr year * * * Days Taken * * * * * * * * * Days Remaining
8 * * 17 * * * * * * * * * * * * * * * * * * * 61=D11
19.67=A10-D11
9 * Total Days allowed
10 *=5*15+(C8-5)*17
11 *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 mannually 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 ?

Thank you in advance

Tia- Hide quoted text -

- Show quoted text -

Im Sorry for the mistake this is corrected table
B
C D
4 Name Selim Gergi Khalil
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

A10 =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 mannually putting the formula i want it to deduct the
previous 5 years as pr the rules =5*15+(C8-5)*17