Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
Subracting Dates to come up with the # of days between them | Excel Worksheet Functions | |||
How do I find how many business days are between two dates | Excel Worksheet Functions | |||
can I use a range of dates as a criteria when using sumif? | Excel Worksheet Functions | |||
I have a list of dates that I need to count based on a date range | Excel Worksheet Functions |