On Jul 24, 8:21*pm, "Bob Phillips" wrote:
BTW, does the 5th year only count as 15 days or 17 days? Similarly, Is the
10th 17 or 19? So for example, is someone joining on 24th July 2002
entitled to 92 days or 94?

--
__________________________________
HTH

Bob

"Bob Phillips" wrote in message

...



Another variation, part years now!

I can get closer, but not exactly what you get because you are using two
different yardsticks. This formula

=IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()-C70.25*365,15))*)

is using a 365 day year, not totally accurate, but not far, but this one

=(NOW()-C7)/30/12

uses a 360 day year, which is a huge 1.37% off. *Using a consistent 365
day formula I get 78.53 for 5th May 2003.

What do you want to do?

--
__________________________________
HTH

Bob

"Tia" wrote in message
....
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- Hide quoted text -

- Show quoted text -

The 5 th year is counted as 17 days and teh 10th as 19 days
If i uses this formula =5*15+(C8-5)*17 on the following date 24 Jul
2002 will gate 93.58 days

but what i cant still find out is a formula that gives me this total
automaticly whitout me putting it whenever an employee reaches his 5
th,10th or 15 th year as qan employee in our company