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
Heath Sanders
edited 12/09/19 in Archived 2017 Posts

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

90 -.PNG

Comments

  • Heath Sanders
    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.

    Thank You in advance 

     

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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. 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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?

  • Heath Sanders
    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

  • Heath Sanders
    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"))))))

    Capture1.PNG

    Capture2.PNG

  • Heath Sanders
    Options

    Also #Incorrect when cell is empty 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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"))))

  • Heath Sanders
    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  

    Red.PNG

    Yellow.PNG

    Green.PNG

    In Pro.PNG

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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"

  • Heath Sanders
    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")))))

  • Heath Sanders
    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")))))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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.