Range of dates determine days

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm looking for a formula that will allow me to enter an anniversary date and
have Excel calculate how many vacation days an employee has earned. Example:
If I put a date that is before 1979 in cell A1 than cell A2 will read 35, if
the date in A1 is between 1980 and 1984 than cell A2 will read 30, and so on
until I reach the present year. I would like to able to enter the date as
month, day, year in the same cell put I could just use 1 cell for month and
day and another for the year.
 
One possible way

=IF(A1="","",VLOOKUP(YEAR(A1),{0,35;1979,30;1985,25;1991,20;1996,15;2001,10},2))

this will allow you to type in the full date in A1
change the different year ranges if necessary. I would be interested where
in the US you get that many vacation days?

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Now that I look at the numbers, they are inflated. I wish they were that
high. Actually the max is 30 days. Thanks for the help......
 
Back
Top