Complicated If Statement

[Lindsay0385]

Hello,

This is my first time posting here and I'm hoping someone can help me. When I usually come across an Excel issue I'm not familiar with I search Google and usually find something that works. But I couldn't find anything similar to what I'm trying to accomplish.

I have a two column spreadsheet, with Column A, "Status", and Column B, "Ready". My goal is to have Column B update automatically based on the value of Column A and the current date. Column A, "Status", has data validation with the selection of "In Pipeline", "2016/Q4", "2017/Q1", "2017/Q2", "2017/Q3", "2017/Q4", and "2018/Q1". Column B, "Ready", will need a value of "X" if the status is "In Pipeline", in the current quarter, or in a past quarter.

Right now I'm manually updating the "Ready" column, but that will be cumbersome in the future. I have a formula that will work to update if the status is "In Pipeline" or in the current quarter, but I cannot figure out how to include past quarters in the formula. Below is an example of my spreadsheet and the formula in Column B.

Formula in B2: =IF(OR((A2=(YEAR(TODAY())&"/Q"&INT((MONTH(TODAY())+2)/3))),A2="In Pipeline"),"X","")
(I found a formula for generating the quarter online and formatted it to fit how I need the status displayed)

Right now the formula works fine, but in January the "2017/Q1" status will have the X's in Column B (which is what I want), but the "2016/Q4" X's will disappear (which I don't want). I can manually update the IF statement to include another OR each quarter (ex. add OR(A2="2016/Q4")), but I wanted to see if anyone else has an idea that would work so I don't have to remember to update the formula each quarter.

Thanks for your help. Hopefully someone out there can help!

Lindsay

 

[James006]

Hi Lindsay,

You could give a try to the following formula :

=IF(A2="In Pipeline","X",IF(OR((A2=(YEAR(TODAY())&"/Q"&INT((MONTH(TODAY())+2)/3))),VALUE(LEFT(A2,4))<YEAR(TODAY())),"X",""))

HTH

 

[Tetra201]

Try this:

=IF(OR((TODAY()>=DATE(--LEFT(A2,4),RIGHT(A2)*3-2,1),A2="In Pipeline"),"X","")

 

[Lindsay0385]

Hi James - I tried your formula, but I couldn't get it to work.

Hi Tetra - Your formula works for the quarter statuses, thank you! But for some reason it returns a #VALUE! error for the "In Pipeline" status, which is odd. It looks to be formatted correctly. But I changed it slightly to the following and it works! Thank you, thank you!

=IF(A2="In Pipeline","X",IF(OR((TODAY()>=DATE(--LEFT(A2,4),RIGHT(A2)*3-2,1))),"X",""))

Thank you both! I appreciate your help!

 

[Tetra201]

... formula works for the quarter statuses, thank you! But for some reason it returns a #VALUE! error for the "In Pipeline" status...

Oops, I just copied a wrong formula. It should have been this:

=IF(OR(TODAY()>=IFERROR(DATE(--LEFT(A1,4),RIGHT(A1)*3-2,1),10^10),A1="In Pipeline"),"X","")

Glad you've found your own workaround.