Automate RYGB based on date

jbusch30
jbusch30 ✭✭✭
edited 12/09/19 in Smartsheet Basics

Hi Everyone, 

I have been trying to get my RYGB to automate based on the tasks dates. 

I would like from the start date to 8 days to be Green...More than 10 days from start to be Yellow and the last 5 days before the end to be Red. I have read many other threads and I just can't wrap my head around this one. Any help would be great.

Comments

  • The logic I used is as follows:

    If today's date is 5 days from the end, then the status is red.

    Else, if today's date is greater than 10 days from start, then the status is yellow. 

    Else, the status is green.

    In this case, the formula in the status column would be 

    =IF(Finish2 - TODAY() < 5, "Red", IF(TODAY() - Start2 > 10, "Yellow", "Green"))

    I don't think that is your exact logic, but this might be a good start. If you can correct my assumption of your logic, I might be able to give you the formula you need.

  • jbusch30
    jbusch30 ✭✭✭

    Yes that logic that you have works, I have tried the formula and still I get #UNPARSABLE

  • are you sure that start2 and finish2 are dates?

    try checking using the ISDATE function

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Make sure that if your column names have spaces or end in numbers that you surround the column name with brackets. Also, make sure that you don't compose your formulas in a word processor like Microsoft Word because it will frequently replace straight quotes with Smart Quotes that Smartsheet does not like. I recommend using a text editor like notepad, or notepad++ which also highlights your parenthesis so you can be sure you have the right number of paired parentheses. 

  • jbusch30
    jbusch30 ✭✭✭

    Thanks I have checked and confirmed that they are dates

     

  • What are the column names you have for the dates?

    What is the column type you are putting the formula?

    Which row are you trying the formula?