Predict future date using "IF" function and multiple criteria using drop down list

[showzow420]

Hello!

Please see attached Excel worksheet.

In Column A I have a drop down list with Three different Criteria - 1_Year, 6_Months, 3_Years. I'd like to be able to have a function that predicts the future date in Column C based off of the date entered in Column as well as the criteria in Column A. So For example:

If A3 is 1_year, and B3 date is 12/1/2020, then C3 should equal 12/1/2021. However, If A3=3_year, C3 should then be 12/1/2023 and so forth.

I had started off with trying to input the function into the cell as: =IF(A3="1_Year",DATE(YEAR(B3)+1,MONTH(B3),DAY(B3)))
It worked! But I quickly realized it wouldn't let me add onto it for the other 2 criteria, unless I'm missing something.

Please Help

[Pete_UK]

You can use this in C3:

=IFERROR(DATE(YEAR(B3)+INDEX({1;3;0},MATCH(A3,{"1_Year";"3_year";"6_Months"},0)),MONTH(B3)+IF(A3="6_Months",6,0),DAY(B3)),"")

Format as a date in the style you prefer.

Hope this helps.

Pete

[showzow420]

Thank you so much! It is working for calculating the "1_year" and "6_month" marks, but no the "3_year" mark. Sorry I'm not very skilled at excel at all! Is there something else I need to input into the formula?

[Pete_UK]

I've attached your workbook with the formula in C3 - it works for me.

Hope this helps.

Pete

[showzow420]

Perfect Thank you!!


Kel

[Pete_UK]

Glad to hear it, and happy to help. Thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete