Options

• Options

Hi Paul

I have another formula I would really appreciate your assistance with - I need to create a “Task Status” Formula based on the criteria below.

COMPLETE

100% Complete

Color - Light Green

ON TRACK

Less than 100% Complete, not yet in the last quarter of delivery date period (4th Quarter of Start Date to End Date period)

Color - Light Green ball

IN PROGRESS BUT DELAYED

Less than 100% completion, and in the 4th quarter of delivery date (4th Quarter of Start Date to End Date period)

Start date reached, and still at 0%

Color - Yellow ball

NOT YET DUE

Start date not reached

Color - Light Blue ball

NOT ON TRACK

Less than 100% and end date is reached

Color - Red ball

• ✭✭✭✭✭
Options

If you think about your criteria, other than being 100% complete the only thing that is relevant is the ball color. So...

=IF([% Complete]@row=1,"Complete", IF(colour@row="Green","On Track",IF(colour@row="Yellow","In Progress But Delayed",IF(colour@row="Blue","Not Due Yet",IF(colour@row="Red","Not on Track","")))))

• Options

Hi

Thanks for the suggestion, however, there nothing in the formula that relates to the quarter in which "today" is, in relation to the "Start Date" & "End Date"

Eg. - Less than 100% completion, and in the 4th quarter of delivery date (4th Quarter of Start Date to End Date period)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!