Annual Leave automatic calculation

[Tia]

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()-C7>10*365,19,IF(TODAY()-C7>5*365,17,IF(TODAY()-
C7>0.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

 

[Gary Brown]

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]

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











- 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