At Risk Flag

Mark Cronk

Ooops. Let me go back to the logic you want:

Blue = Complete, so %Complete =1

Yellow=Not Started, so %Complete =0

Red = Delayed - Finish@row<[Due Date]@row=TODAY(7)? I took this to mean that if the finish date is after the due date or if we are within 7 days of the due date you want it Red.

Green = In Progress with is Not any of the above

Here's the formula I get:

=IF([%Complete]@row = 1, "Blue", IF([%Complete]@row = 0, "Yellow", IF(Finish@row > [Due Date]@row, "Red", IF(TODAY(7)>[Due Date]@row , "Red", "Green")))))

Are we closer? If you get an error, what error do you get?

Mark

Mark Cronk

Let's take a different approach with your At Risk column. Try this formula:

=IF(AND([due date]@row <= TODAY(7), [%Complete]@row < 1), 1, IF(AND([due date]@row >= TODAY(), [%Complete]@row < 1), 1, IF(Status@row = "Red", 1, 0)))

It Flags at At Risk if:

• The due date is 7 days or less away and it's not complete
• The due date is today or in the past and it's not complete
• the project is delayed as determined by Red in the Status

I hate to ask, work?

Mark

Cee Johnson

Mark- I greatly appreciate your patience with my inexperience with Smartsheet formulas especially this one. Oh my, can't believe how long it took to crack the code but here it is and it works. Finally! I refused to give up. I definitely need to take a break.

The formula for RGYB Status column based on Finish, Due Date and %Complete columns is :

=IF([%Complete]@row = 1, "Blue", IF([%Complete]@row = 0, "Yellow", IF(Finish@row > [Due Date]@row, "Red", IF(TODAY(7) > [Due Date]@row, "Red", "Green"))))

I removed a parenthesis off the end and it works.

See highlighted area in screenshot.

Mark Cronk

Well done. Happy to help along the way. Thanks for using the Community.

Until next time,

Mark

Cee Johnson

Not sure about At Risk formula yet. The row is flagged when the Finish column date is blank. See highlighted screenshot.

Mark Cronk

Found the mistake. The second AND statement was greater than today. As I look at it, we didnt need that statement. Try:

=IF(AND([due date]@row <= TODAY(7), [%Complete]@row < 1), 1, IF(Status@row = "Red", 1, 0))

Thanks for making me think and allowing me to help.

Mark

Cee Johnson

Mark,

This has been a journey and w/out you, idk but greatly appreciate you hanging in here with me. IT WORKS! See screenshot. Can I reach out to you in the future for this item if I need additional help? Thank you for all of your time and help on these 2 formulas. Also, be safe, warm and enjoy the holidays!

Mark Cronk

Hi @Cee Johnson ,

Look beautiful. Well done. I'm happy to help anytime. Just tag me in your post so I see it.

Be Well,

Mark

Cee Johnson

@Mark Cronk

Hi Mark,

Hope all is well. I created tasks with 2021 dates on a project plan but I don't see the dates on the Gantt chart. Please confirm if this is correct or there's something I need to do for it to display on the Gantt.

Mark Cronk

Good afternoon @Cee Johnson ,

A Gantt chart requires 2 date columns. Congirm you column are formatted as dates. Then, click the Project Settings icon in the Gantt chart header to ensure the Gantt chart is using the correct columns for start and end date.

More on Gantt charts here:

https://help.smartsheet.com/articles/765675-work-with-gantt-chart

FInd your problem?

Mark

Cee Johnson

@Mark Cronk

Hi Mark,

I often don't see your comments right away and sometimes not until hours later. Guess the page has to be refreshed. The columns setting is for dates but the Gannt chart still doesn't show 2021 dates. Only 2020.

Todd Smelser

I am using this formula to set the at Risk Flag

=IF(AND([Due Date]@row < TODAY(), NOT(Status@row = "Complete")), 1, 0)

However, Rows with no date (like a summary row) will set to 1 because the date is blank. How do I tell the formula to ignore a blank cell?

Mark Cronk

You can look for a date with ISDATE or for a blank with ISBLANK

Try:

=IF(AND(ISDATE([due date]@row), [Due Date]@row < TODAY(), NOT(Status@row = "Complete")), 1, 0)

Work?

Mark