Range of dates determine days
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,2 5;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) "DNSNIDER" wrote in message ... 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. |
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...... "Peo Sjoblom" wrote: One possible way =IF(A1="","",VLOOKUP(YEAR(A1),{0,35;1979,30;1985,2 5;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) "DNSNIDER" wrote in message ... 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. |
All times are GMT +1. The time now is 07:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com