Formula to return Vacation weeks

S

Sharon

I am having trouble with creating a formula to return # of weeks of vacation
based on anniversary year and current year.

The formula that I have created is as follows however it works but I need it
to look at the anniversary dates YEAR only.

Start year 1986 -- 5 weeks vacation
Start year 1995 -- 4 weeks vacation
Start year 2003 -- 3 weeks vacation
Any year greater than 2003 -- 2 weeks vacation

I always build my formulas in pieces and keep added to it as each condition
is formulating correctly. I need the formula to look at the anniversary
dates year only for this to work.
1st if statement works, and second if statement works until I got to the
anniversary date 7/14/86 and returns false and not 4 (for 4 weeks if year
1986). This is how I found out that excel is looking at the entire date.
Month/day/year. How do I get around that?

D2 = Anniversary Date
'Date Parameters'A2 = 1/1/1986
'Date Parameters'A3 = 1/1/1987
'Date Parameters'B3 = 1/1/1995

=IF(D13<='Date Parameters'!$A$2,5,IF(AND(D13<='Date
Parameters'!$A$3,D13>='Date Parameters'!$B$3),4))

First time on this forum and I'm looking forward to see your responses.
 
R

Ron@Buy

Sharon
I'm not sure I can come up with an answer to your problem but you have not
given us all the information to help give you a solution.
What data does cell D13 contain? What is the formula that is looking at the
entire date > To calculate using the month try =MONTH(D2)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top