Automate RYGB based on Follow-up Date by Week

sdkotes12901

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" 

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

 

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

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

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

 

 

SmSulli

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

Shawn

 

sdkotes12901

Thank you!