Calculated Date Column with Nested IF statements

Question

I am trying to calculate a Date based on two different columns in a SharePoint List: RANK column and PEBD column.

Essentially, I need to do the following:

IF RANK is E1, then the value of the column is PEBD + 6 YEARS - 1 DAY

OR

IF RANK is E3, then the value of the column is PEBD + 10 YEARS - 1 DAY

OR

IF RANK is E6, then the value of the column is PEBD + 22 YEARS - 1 DAY

I need to max out the 7 allowable nested statements and use the "IF FALSE" argument to return an 8th calculated date.

Here is what I came up with. The formula works with a single IF statement, but I haven't found a way to make nested statements work. TIA for any help. Code below:

=IF([Rank]="E1",DATE(YEAR([PEBD])+6,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E2",DATE(YEAR([PEBD])+6,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E3",DATE(YEAR([PEBD])+10,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E4",DATE(YEAR([PEBD])+12,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E5",DATE(YEAR([PEBD])+20,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E6",DATE(YEAR([PEBD])+22,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E7",DATE(YEAR([PEBD])+24,MONTH([PEBD])+0,DAY([PEBD])-1)),DATE(YEAR([PEBD])+26,MONTH([PEBD])+0,DAY([PEBD])-1)))

UPDATE:

I've started to explore some alternatives to the outcome I'm seeking. For example, adding more calculated columns to achieve the same result. One possibility would be to have one column with nested IF statements to simply return the desired YEARS for the specified RANK, rather than ask the calculated column to calculate the entire date. Then, another calculated column that adds the YEARS returned with the PEBD date column mentioned above. The only problem I'm running into is that this calculation doesn't return an accurate date.

It seems if I only calculate years (i.e. IF RANK is E1, then return "6") and add that column to the PEBD date column, it only adds that number of days to the date. So instead, I did the math for the number of days in the specified number of years and added that to the formula, but that returns an inaccurate date. The date should always be the number of years later MINUS 1 day (i.e. 6/10/2010 new calculated date should be 6/9/2009).

Any outside of the box recommendations are welcome.

Here is the code that calculates the number of days:

=IF(Rank="E1","2189",IF(Rank="E2","2189",IF(Rank="E3","3649",IF(Rank="E4","4379",IF(Rank="E5","7299",IF(Rank="E6","8029",IF(Rank="E7","8759",9489)))))))

The code that adds the two columns together is simply "=PEBD+column1"

Answer 1

FINAL UPDATE:

The solution, for now, will be multiple calculated columns to achieve the same result. Here is the breakdown:

Code for first calculated column, "HYT Calculation" (calculates number of years associated with RANK):

=IF(Rank="E1","6",IF(Rank="E2","6",IF(Rank="E3","10",IF(Rank="E4","12",IF(Rank="E5","20",IF(Rank="E6","22",IF(Rank="E7","24",26)))))))

Code for second calculated column (adds number of years from first calculated column to the "PEBD" date column):

=DATE(YEAR(PEBD)+[HYT Calculation],MONTH(PEBD),DAY(PEBD)-1)

This returned the date exactly as I needed it, though I would prefer to not have to use multiple columns if using one is possible. I'll update again if I find a way to make the first method work.

Answer 2

Here is the answer to the original question, curtesy of @willman.

"There is no reason why you couldn't combine those two: =DATE(YEAR(PEBD)+(IF(Rank="E1","6",IF(Rank="E2","6",IF(Rank="E3","10",IF(Rank="E4","12",IF(Rank="E5","20",IF(Rank="E6","22",IF(Rank="E7","24",26)))))))),MONTH(PEBD),DAY(PEBD)-1)"