IF(AND..........also ISDATE combo help please

Heath Sanders

Hi Team,

I'm attempting to build a formula that requires three parts (I Think). The attachment below demonstrates in simple terms. When the production manager enters the date ("90 Day Defects Period") and clicks on the drop list ("PRODUCTION Job Status" and choose ("DEFECTS 90") the 90 days period will kick in. I want to track this automatically, I will do this by entering another row which capture dates and returns a word "Red" or "Orange" when those dates are reached from the (90 Day Defects date chosen.

i.e. =IF(ISDATE([90 Day Defects Period]1), DATE(YEAR([90 Day Defects Period]1), MONTH([90 Day Defects Period]1) + 1+1, >30 = "red", > 60 = "Orange", > 7 ="Green". 

"90 Day" row will return Red, Orange, Green  Then "(90 Day Defects") cell will use the conditional formatting will change color to that color giving us a view when the jobs are at with this. 

My attempt at starting below....Don't laugh Craig.... haha

 

=IF(ISDATE([90 Day Defects Period]1), DATE(YEAR([90 Day Defects Period]1), MONTH([90 Day Defects Period]1) + 1, 30), ""),IF(AND(ISDATE([90 Day Defects Period]1),[PRODUCTION Job Status]1="DEFECTS 90", DATE(YEAR([90 Days Date]1), MONTH([90 Days Date]1) +1, 90), "")

Heath Sanders

P.s

When the days are between 90 and 45, Red, between 44 and 8, Orange and the last 7 it will go green.

Thank You in advance 

 

Mike Wilday

What should be the measure again? 90 days from the date entered in the 90 days defect period? or is the date entered the end of the 90 day period? 

If its 90 days from the 90 day defect period, this should do the trick: 

=IF([90 Day Defect Period]10 + 90 - TODAY() > 45, "Red", IF(AND([90 Day Defect Period]10 + 90 - TODAY() < 45, [90 Day Defect Period]10 + 90 - TODAY() > 8), "Yellow", IF(AND([90 Day Defect Period]10 + 90 - TODAY() < 9, [90 Day Defect Period]10 + 90 - TODAY() > 0, "Green", IF(ISBLANK([90 Day Defect Period]10), " ", "90 Day Period over")))))

Just replace the row number of 10 with whatever row you're testing on. 

Mike Wilday

Basically, I am taking the defect period, adding 90 days, and subtracting today to determine how many days are left and comparing it to the range you requested. 

Is that what you intended?

Heath Sanders

Hi Mike,

Your a legend mate. That's exactly what I'm after. I'm about to enter the code and see how it all works out. 

The only thing left is to have Smart Sheet allow the formulas to be sent to the other sheet. When and If this happens, every department from the first interaction with the customer through to the completion of the job will be tracked and recorded...then we can develop reports from that.

Thanks Mike

Heath Sanders

Hi Mike,

In bold it works great, returns #Incorrect from there ?

=IF([90 Day Defects Period]1 + 90 - TODAY() > 45, "Red", IF(AND([90 Day Defects Period]1 + 90 - TODAY() < 45, [90 Day Defects Period]1 + 90 - TODAY() > 8), "Yellow", IF(AND([90 Day Defects Period]1 + 90 - TODAY() < 9, [90 Day Defects Period]1 + 90 - TODAY() > 0, "Green", IF(ISBLANK([90 Day Defects Period]1, "", "90 Day Defects Over"))))))

Heath Sanders

Also #Incorrect when cell is empty 

Mike Wilday

I see the issue. I failed to close the final AND statement. 

Try this: 

=IF([90 Day Defect Period]10 + 90 - TODAY() > 45, "Red", IF(AND([90 Day Defect Period]10 + 90 - TODAY() < 45, [90 Day Defect Period]10 + 90 - TODAY() > 8), "Yellow", IF(AND([90 Day Defect Period]10 + 90 - TODAY() < 9, [90 Day Defect Period]10 + 90 - TODAY() > 0), "Green", IF(ISBLANK([90 Day Defect Period]10), " ", "90 Day Period over"))))

Heath Sanders

Brilliant work Mike. There's one thing that dawned on me.

PRODUCTION Job Status is in any other state other than "DEFECTS 90" , lets say "IN PROGRESS" and a date is entered by mistake then "90 Day" will return (""), is that easy to ad to formula ?

 

Thanks Again Mike  

Mike Wilday

=IF([PRODUCTION Job Status <> "DEFECTS 90", "", IF([90 Day Defect Period]10 + 90 - TODAY() > 45, "Red", IF(AND([90 Day Defect Period]10 + 90 - TODAY() < 45, [90 Day Defect Period]10 + 90 - TODAY() > 8), "Yellow", IF(AND([90 Day Defect Period]10 + 90 - TODAY() < 9, [90 Day Defect Period]10 + 90 - TODAY() > 0), "Green", IF(ISBLANK([90 Day Defect Period]10), " ", "90 Day Period over")))))

Give that at try. It should only produce results if the Job status = "DEFECTS 90"

Heath Sanders

#INCORRECT ........not far off 

=IF([PRODUCTION Job Status]1, <>"DEFECTS 90", "", IF([90 Day Defects Period]1 + 90 - TODAY() > 45, "Red", IF(AND([90 Day Defects Period]1 + 90 - TODAY() < 45, [90 Day Defects Period]1 + 90 - TODAY() > 8), "Yellow", IF(AND([90 Day Defects Period]1 + 90 - TODAY() < 9, [90 Day Defects Period]1 + 90 - TODAY() > 0), "Green", IF(ISBLANK([90 Day Defects Period]1), "", "90 Day Defects Over")))))

Heath Sanders

I cracked it Mike ....thanks again mate. Are you a white wine drinker (Nz has some of the best in the world) 

 

=IF([PRODUCTION Job Status]1 <> "DEFECTS 90", "", IF([90 Day Defects Period]1 + 90 - TODAY() > 45, "Red", IF(AND([90 Day Defects Period]1 + 90 - TODAY() < 45, [90 Day Defects Period]1 + 90 - TODAY() > 8), "Yellow", IF(AND([90 Day Defects Period]1 + 90 - TODAY() < 9, [90 Day Defects Period]1 + 90 - TODAY() > 0), "Green", IF(ISBLANK([90 Day Defects Period]1), "", "90 Day Defects Over")))))

Mike Wilday

Hi Heath, Glad I could help! No, I don't drink white wine. But I know many who do and will remember it. Thanks.