Massive =IF together with =DATE formula

[beseda2004]

Hello.
I am trying to create a formula which will have date as an output depending on variable.
Variable are following:

4
5
L6
U6

The idea is to add a certain amount of years and month to the TODAY date, depending on the variable.
The output date (month and day) are pre-defined = 1 of May
Though the year varies. It will be 4 years for 4, 3 years for 5, 2 years for L6 and 1 year for U6.
The trick is that most students will be registered in september, but some might register after new year and this is where I am not sure of how to make year add up correct.
I tried to make up this formula which gives output of 42491 =\


=IF(B5=3; DATE(YEAR(C11)+4;MONTH($D$2);DAY($D$2)); IF(B5=4;DATE(YEAR(C11)+4;MONTH($D$2);DAY($D$2)); IF(B5=5;DATE(YEAR(C11)+3;MONTH($D$2);DAY($D$2)); IF(B5=L6;DATE(YEAR(C11)+2;MONTH($D$2);DAY($D$2)); IF(B5=U6;DATE(YEAR(C11)+5;MONTH($D$2);DAY($D$2));0)))))

[arlu1201]

Your question is not very clear. What are you trying to accomplish? Do you have an excel file that you can attach that will help explain your issue?

[beseda2004]

Microsoft Excel Worksheet.xlsx
Here it is with more clarified question inside

[Pete_UK]

Put the values 4,3,2,1 in cells L2 to L5, and then you can use this formula in B5:

=IF(B2="","",DATE(YEAR(B4)-IF(AND(MONTH(B4)>=1,MONTH(B4)<8),1,0)+VLOOKUP(B2,K$2:L$5,2,0),5,1))

If the current date is between January and August of one year, then the year values are subtracted by one year.

Hope this helps.

Pete

EDIT: I've attached your workbook with the changes in it.

[beseda2004]

Soz Pete dooesn't want to work for some reason... Says that formula contains error, thou I can't see it.

[Pete_UK]

Have you looked at the file that I attached?

Pete

[beseda2004]

Thanks Pete worked well!

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

PS You might like to mark the thread as Solved.