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

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

Hello!

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.

2. ## Re: Predict future date using "IF" function and multiple criteria using drop down list

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

3. ## Re: Predict future date using "IF" function and multiple criteria using drop down list

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?

4. ## Re: Predict future date using "IF" function and multiple criteria using drop down list

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

Hope this helps.

Pete

5. ## Re: Predict future date using "IF" function and multiple criteria using drop down list

Perfect Thank you!!

Kel

6. ## Re: Predict future date using "IF" function and multiple criteria using drop down list

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

Pete

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1