excell

[CATHERINE]

Hello,
Iam trying very hard to find a fourmula in order to calculate holiday

for my employees.
The problem is as follow...
If they are in the company for less than 2 years they are entitle to
20 days between the 1st of January and the 31st of december
If they are in the company between 2 and 5 years they are entitle to
22 days from their anniversary date.
If they are in the company for more than 5 years they are entitle to
25 days from their anniversary date.

My problem is to take in consideration their anniversary date (ex
starting date the 15 of march 2000 how many holidays does this perso

get?)

If anyone could get back to me on this one I will be very gratfull :
Here is the formula I have for the 20 day
=((((IF(C7<>0,IF(YEAR(C7)<YEAR(NOW()),20,ROUNDUP(1.66*(12-MONTH(C7)+(IF(MONTH(C7)<>2,IF(MONTH(C7)>7,IF(MOD(MONTH(C7),2)>0,30,31),IF(MOD(MONTH(C7),2)>0,31,30)),(IF(MOD(YEAR(C7),4)>0,28,29)))-DAY(C7)+1)/IF(MONTH(C7)<>2,IF(MONTH(C7)>7,IF(MOD(MONTH(C7),2)>0,30,31),IF(MOD(MONTH(C7),2)>0,31,30)),(IF(MOD(YEAR(C7),4)>0,28,29)))),3)),0)))*A7)-Q7)-((((IF(V7<>0,IF(YEAR(V7)<YEAR(NOW()),20,ROUNDUP(1.66*(12-MONTH(V7)+(IF(MONTH(V7)<>2,IF(MONTH(V7)>7,IF(MOD(MONTH(V7),2)>0,30,31),IF(MOD(MONTH(V7),2)>0,31,30)),(IF(MOD(YEAR(V7),4)>0,28,29)))-DAY(V7)+1)/IF(MONTH(V7)<>2,IF(MONTH(V7)>7,IF(MOD(MONTH(V7),2)>0,30,31),IF(MOD(MONTH(V7),2)>0,31,30)),(IF(MOD(YEAR(V7),4)>0,28,29)))),3)),0)))*A7)


:lol

 

[Dave O]

Good morning, Catherine-
Will a slightly more simplistic view work? I mocked up a formula with
three columns: a header in A2 that says "Start date"; a header in B2
that says "Employed Years", and a header in C2 that says "Vacation
days". In A3 I used your sample date, March 15 2000. I used this
formula in B3:
=(TODAY()-A3)/365
.... which generates a fractional number of years the employee has
worked. This is the oversimplification: it assumes 365 days in every
years, which is not always the case but arguably has little impact on
the intent of the calculation.

The formula in C3 is
=IF(B3<2,20,IF(AND(B3>=2,B3<=5),22,IF(B3>5,25,"")))
.... which tells the allowed number of vacation days.

Does that do it for you?

 

[Sandy Mann]

Catherine,

I am making some assumptions:

If they are in the company for less than 2 years they are entitle to
20 days between the 1st of January and the 31st of december

I assume that, as in my company, only whole years between 1st January and
31st December are counted. This means that in the first part year, (in your
example between 15th March until the 31st December you get a proportion,(0.6
days) for every month - 15 days in the first part year. After that all
holidays are calculated assuming that the employee will be employed until
31st December. Therefore in your example in the year 2001 the employee
would be due 20 days.



To simplify the formula I used a helper cell, (E1), to calculate the number
of complete years for use in the calculation. If you do not want to use a
helper cell then you will have to replace E1 the formula with the formula in
Cell E1. The employee's start date is in D1 and the formula to calculate
the holidays due is:

=IF(E1=0,INT(DATEDIF(D1,DATE(YEAR(D1),12,31),"m")/12*20),(E1>0)*20+(E1>=2)*2
+(E1>=5)*3)

HTH

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

Hello,
Iam trying very hard to find a fourmula in order to calculate holidays

for my employees.
The problem is as follow...
If they are in the company for less than 2 years they are entitle to
20 days between the 1st of January and the 31st of december
If they are in the company between 2 and 5 years they are entitle to
22 days from their anniversary date.
If they are in the company for more than 5 years they are entitle to
25 days from their anniversary date.

My problem is to take in consideration their anniversary date (ex
starting date the 15 of march 2000 how many holidays does this person

get?)

If anyone could get back to me on this one I will be very gratfull :
Here is the formula I have for the 20 days

=((((IF(C7<>0,IF(YEAR(C7)<YEAR(NOW()),20,ROUNDUP(1.66*(12-MONTH(C7)+(IF(MONT
H(C7)<>2,IF(MONTH(C7)>7,IF(MOD(MONTH(C7),2)>0,30,31),IF(MOD(MONTH(C7),2)>0,3
1,30)),(IF(MOD(YEAR(C7),4)>0,28,29)))-DAY(C7)+1)/IF(MONTH(C7)<>2,IF(MONTH(C7
)>7,IF(MOD(MONTH(C7),2)>0,30,31),IF(MOD(MONTH(C7),2)>0,31,30)),(IF(MOD(YEAR(
C7),4)>0,28,29)))),3)),0)))*A7)-Q7)-((((IF(V7<>0,IF(YEAR(V7)<YEAR(NOW()),20,
ROUNDUP(1.66*(12-MONTH(V7)+(IF(MONTH(V7)<>2,IF(MONTH(V7)>7,IF(MOD(MONTH(V7),
2)>0,30,31),IF(MOD(MONTH(V7),2)>0,31,30)),(IF(MOD(YEAR(V7),4)>0,28,29)))-DAY