calculating Leave

[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
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()-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 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

 

[Bob Phillips]

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.

 

[Tia]

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










- 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

 

[Bob Phillips]

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

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










- 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

 

[Tia]

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






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

 

[Bob Phillips]

How about this to calculate the total leave entitlement to-date

=IF((TODAY()-C7)>365/4,
((ROUNDUP((TODAY()-C7)/365,0)>5)*15*ROUNDUP((TODAY()-C7)/365,0))
+((ROUNDUP((TODAY()-C7)/365,0)>10)+(ROUNDUP((TODAY()-C7)/365,0))-5)*2,0)

--
__________________________________
HTH

Bob

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






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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Tia]

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)







- 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

 

[Bob Phillips]

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

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)






- 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

 

[Bob Phillips]

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?

 

[Tia]

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










- 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

 

[Bob Phillips]

Well, you have pointedly ignored my point about the different yardsticks,
and persist in pushing that ludicrous formula that calculates based on a 12
month/30 day year (i.e. a year short of 5 or 6 days). If you used

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

it would be more accurate but still less accurate than using 365, so I am
going to ignore that, offer what I have, and you can do as you will.

Working straight off of the employment date,


=ROUND(((TODAY()-C7)/365*15)+(INT(MAX(0,(TODAY()-C7)/365-5))*2)+(INT(MAX(0,(TODAY()-C7)/365-10))*2),2)

--
__________________________________
HTH

Bob

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










- 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