RYG Formula Based On the Specific Date in a Field

ScottA5236

Hello -

I'm new at Smartsheet and have been looking for a RYG formula that's only based on the date in the "Date Due" column. I've searched and have asked several folks on my team. They all point to the "Today" formula, but they're not sure what that would be and it seems more complicated than basing a formula on a specific field date. For instance, if something was due last November, it would be red, and, if due next week, yellow. Due in 3 months? Green.

Based on the single date in a field, I'd like it to be green to start. Then, turn yellow when it's within 15 days of that specific due date and then red once it is past the due date. Seems simple, but, again, new to this.

Much appreciated -

Scott

Ryan Sides

Hey Scott, are you looking to define a date on your sheet and then have all the other dates in your sheet compare to that one to determine their RYG color? If so, use this...

=if([Due Date]@row >[My Date]#, "Red", if( [Due Date]@row +16 >[My Date]#, "Yellow", "Green"))

To create this [MyDate]# field, click on the "Summary" icon to the far right on your sheet. A right side bar will slide out. Create the field , give it the My Date name and click the Date Type.

Is that what you're looking for?

ScottA5236

Hi Ryan -

Good to hear from you, thank you. For this I'm only looking at a single calendar date in a cell and not comparing it to another date, only itself. I've got a "Due Date" column set up were the column type is "Date".

I like what you're showing here. I wonder if I could just use a part of it - since there's no comparison cell/value - that would work. ?? So, it would be green until 15 days before the shown date where it would then turn yellow. Once the date is reached it would then turn red.

Another thought, just realizing this now, once it's completed (by another date column or the word "complete" in a dropdown column) how would we turn it grey to show complete, for instance, so the date wouldn't stay red?

Much appreciated -

Scott

Ryan Sides

Ah! Got it. Your coworkers were right, if I'm understanding you correctly now. Try this...

=IF(Status@row = "Complete", "Gray", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row >= TODAY(15), "Green", "Yellow")))

ScottA5236

Hi Ryan -

This looks like it's working, thanks. I'll keep an eye on it.

Have a great weekend and thanks!

Scott

Ryan Sides

You bet! Same to you!