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.

Automate RYGB based on Follow-up Date by Week

sdkotes12901
sdkotes12901 ✭
edited 12/09/19 in Archived 2017 Posts

Community:

I have tried but cannot figure out how to make this formula work. Probably something very simple, and I am overthinking.

Any guidance would be much appreciated.

Thank you,

Sandy

Scenario: Regardless of the day of the week I open my sheet:

If [Follow-up date]1 is between M - F of the current week, RYGB symbol = "Green" 

If [Follow-up date]1 is between M - F of the previous week, RYGB symbol = "Yellow" 

If [Follow-up date]1 is older than M of the previous week, RYGB symbol = "Red" 

Tags:
· Share on FacebookShare on Twitter

Comments

  • SmSulli
    SmSulli ✭

    Hi,

     

    Here's a formula for you to try.  This will go in the RYB column.

    =IF(WEEKNUMBER([Follow-up date]1) = WEEKNUMBER(TODAY()), "Green", IF(WEEKNUMBER([Follow-up date]1) = WEEKNUMBER(TODAY()) - 1, "Yellow", IF(WEEKNUMBER([Follow-up date]1) < WEEKNUMBER(TODAY()) - 1, "Red", "")))

    Not sure what you wanted for weeks in the future, so I made that blank.  Feel free to change it.

    Enjoy

    Shawn

     

    · Share on FacebookShare on Twitter
  • sdkotes12901
    sdkotes12901 ✭

    Shawn:

    Your formula worked beautifully. Thank you, thank you, thank you... however, I did not provide the other criteria.

    Below is my formula (including yours).

    If you can show me what I am doing wrong, I would appreciate your help.

    Sandy

    What I am trying to say is:

    If the date closed is not blank turn symbol blue

    If the status is "resolved" turn symbol blue

    If the status is blank and follow-up date is blank, insert "No Follow-up Date"

    Otherwise, etc. [your formula picks up here]

    =IF([Date Closed]1 <> 0, "Blue", IF(AND(Status1 = "Resolved"), "Blue", IF(AND(Status1 = ""), "", IF(AND([Follow-up Date]1 = ""), "Follow-up Date is Blank", IF(WEEKNUMBER([Follow-up Date]1) = WEEKNUMBER(TODAY()), "Green", IF(WEEKNUMBER([Follow-up Date]1) = WEEKNUMBER(TODAY()) - 1, "Yellow", IF(WEEKNUMBER([Follow-up Date]1) < WEEKNUMBER(TODAY()) - 1, "Red", "")))))))

    · Share on FacebookShare on Twitter
  • SmSulli
    SmSulli ✭

    Here you go...

    =IF(OR([Date Closed]1 <> "", Status1 = "Resolved"), "Blue", IF(AND(Status1 = "", [Follow-up date]1 = ""), "No Follow-up Date", IF(WEEKNUMBER([Follow-up date]1) = WEEKNUMBER(TODAY()), "Green", IF(WEEKNUMBER([Follow-up date]1) = WEEKNUMBER(TODAY()) - 1, "Yellow", IF(WEEKNUMBER([Follow-up date]1) < WEEKNUMBER(TODAY()) - 1, "Red", "")))))

    · Share on FacebookShare on Twitter
  • sdkotes12901
    sdkotes12901 ✭

    Shawn:

    I cannot thank you enough so thank you again. Works beautifully still, but of course, no good deed goes unpunished... my last hurdle is # INVALID DATA TYPE.

    I assume this error is because both the follow-up date and the date closed fields are empty. Would I use ISBLANK and if so, how would I incorporate that?

    Unfortunately, I am a novice (obviously) with formulas and do not even know how to begin trying to hide this message.

    Sandy

     

     

    · Share on FacebookShare on Twitter
  • SmSulli
    SmSulli ✭

    No, if both are blank, "No Follow-up Date" will show.  Check that the rows and columns are correct.

    Shawn

     

    · Share on FacebookShare on Twitter
  • sdkotes12901
    sdkotes12901 ✭

    Thank you!

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories