Vacation Date Problem

[RM270]

Hi. I have an employee that was hired on 4-1-2003.
I use =DATEDIF(E64,TODAY(),"y") to determin that he has worked 6 yrs. When
he reaches his anniversay date, his vacation time will go from 80 hrs to 120
hrs. I use VLookup to determine the vacation time. This all works okay.

The problem is that on January 1, my business posts a list of vacation
allowances for the year. The list shows how much vacation time the employee
gets, regardless of when the anniversay date is. So my above employee will
show that he has 120 hrs of vacation time on January 1. This is only a
problem when the employee is in a year where vacation time changes from one
level to the next.
Vacation time changes when the employee goes from year 6 to 7, year 13 to
14, year 19 to 20 and year 24 to 25. Can anyone help make this work during
those years?

Thanks for any help.

 

[Bob Phillips]

Use a formula like this

=80+IF(DATEDIF(A1,TODAY(),"y")<6,0,IF(DATEDIF(A1,TODAY(),"y")=6,ROUND(40*(DATE(YEAR(Hiredate)+1,0,0)-Hiredate)/(DATE(YEAR(Hiredate)+1,0,0)-DATE(YEAR(Hiredate),0,0)),0),40))

This caters for yesr 6, just add additional IFs for years 13, 19 and 24

Might be a good idea to put these formula

=DATEDIF(A1,TODAY(),"y")

=DATE(YEAR(Hiredate)+1,0,0)

=DATE(YEAR(Hiredate),0,0)

and refernce those cells in the formula, it will be more readable.


HTH

Bob

 

[Bernard Liengme]

As I read you question, you need to know how many year employer will have
worked not on 1 Jan but on 31 Dec
So it I will reach my 7th anniversary in mid-June then on Jan 1 the posted
notice should say I get 120 hours.
How about changing your formula to
=DATEDIF(E65,DATE(YEAR(TODAY()),12,31),"y")
The result will mean: by the end of this year employer will have been here X
years.
best wishes

 

[RM270]

Could you explain this to me Bob? I don't quite understand it. The 80 and 40
confuse me.

Thanks

 

[RM270]

Thank you Bernard. It works perfectly.

As I read you question, you need to know how many year employer will have
worked not on 1 Jan but on 31 Dec
So it I will reach my 7th anniversary in mid-June then on Jan 1 the posted
notice should say I get 120 hours.
How about changing your formula to
=DATEDIF(E65,DATE(YEAR(TODAY()),12,31),"y")
The result will mean: by the end of this year employer will have been here X
years.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



.

 

[Bob Phillips]

I read the problem differently than Bernard, I apportioned the extra 40
hours on the anniversary year so that they got that portion relating to how
far into the 7th year their anniversary occurred.

Bob