If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula to return Vacation weeks
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. |
#2
|
|||
|
|||
Formula to return Vacation weeks
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) "Sharon" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|