Figuring Vacation Hrs. Earned using Current Date minus Hire Date

[Sharon]

I have been trying to figure out a formula that will calculate current
vacation hours according to a person's hire date. I have it partly figured
out, but not exactly. I have tried the IF A1 which is my Current Date less B1
which is my hire date is <365, "0.00", etc. The vacation hrs. are earned
based on 1-2 yrs. = 40 hrs., after 3 yrs.-9yrs. = 80 hrs., after 10yrs. 120
hrs. Does anyone have a formula that will correctly calculate this by hire
date so that the vacation hours do not show until the person's hire date has
passed? Thanks for ANY Help I can get

 

[Peo Sjoblom]

With hire date in A1 try this

=IF(A1="","",VLOOKUP(DATEDIF(A1,TODAY(),"y"),{0,0;1,40;3,80;10,120},2))


try with some test hire dates in A to see if it's what you want

 

[BoniM]

With A1 your current date and B1 your hire date:
=HLOOKUP(A1-B1,{0,365.26,1095.78,3652.6;0,40,80,120},2)

 

[Sharon]

Thanks for the suggestion. I tried this, but it still only gives me the
vacation earned by the year, I need to have it calculate it by the actual
hire date. I need a formula that would also use the month and date so that
the vacation does not appear available until the person's hire date
anniversary. It has been a challenge trying to figure this one out.

 

[Peo Sjoblom]

It does give you vacation earned by the year which is how you presented it,
nowhere in your original post do you say that you want vacation based on
months

My formula will give you 40 hours if it is between 1 and 3 years, between 3
and 10 years it will give you 80 hours and after 10 years 120 hours. It does
calculate by the actual hire date and it will return 0 until after 1 yes,
and then it will change after each range of years as stated in your
original post

 

[BoniM]

With A1 your current date and B1 your hire date:
=HLOOKUP(A1-B1,{0,365.26,1095.78,3652.6;0,40,80,120},2)
This formula returns the appropriate vacation hours for each employee based
on their anniversary date. With todays date entered as 5/03/07, the
following hire dates give these vacation hours:
Hire Date Vacation Hire Date Vacation
5/2/1994 120 5/3/1994 120
5/2/1995 120 5/3/1995 120
5/2/1996 120 5/3/1996 120
5/2/1997 120 5/3/1997 80
5/2/1998 80 5/3/1998 80
5/2/1999 80 5/3/1999 80
5/2/2000 80 5/3/2000 80
5/2/2001 80 5/3/2001 80
5/2/2002 80 5/3/2002 80
5/2/2003 80 5/3/2003 80
5/2/2004 80 5/3/2004 40
5/2/2005 40 5/3/2005 40
5/2/2006 40 5/3/2006 0
5/2/2007 0 5/3/2007 0
In other words, no vacation time added until appropriate anniversary date.
To explain how it works (and I'm sorry I didn't yesterday - was running
late!)
it subtracts the hire date from todays date, which will return the number of
days an employee has been on the job and then matchs the answer to the
approriate value in the first row of the array. In tabular format, it would
look like this:
0 365.26 1095.78 3652.6
0 40 80 120
HLOOKUP finds the largest value that is less than the lookup value and then
it returns the correct number of vacation days from the second row in the
array.
So, if an employee has worked 4 years, which is about 1,461 days, the
largest value that isn't greater is 1095.78, which gives a vacation time of
80 hours.

Hope this helps!

 

[BoniM]

Actually, upon reflection, it doesn't give them their increase until the day
after their anniversary... use this one to update ON their anniversary date.
=HLOOKUP(A2-B2,{0,364.26,1094.78,3651.6;0,40,80,120},2)