#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

Options
edited 12/09/19

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), "")

• Options

P.s

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

• ✭✭✭✭✭✭
edited 11/15/17
Options

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.

• ✭✭✭✭✭✭
Options

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?

• Options

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

• Options

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"))))))

• Options

Also #Incorrect when cell is empty

• ✭✭✭✭✭✭
Options

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"))))

• Options

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

• ✭✭✭✭✭✭
Options

=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"

• Options

#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")))))

• Options

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")))))

• ✭✭✭✭✭✭
Options

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

This discussion has been closed.