Update Status and Progress of an activity based on Actual Start and End Date

Jameel Nahas · 09/19/20

Hi ,

I am relatively new to smart-sheet and would require assistance in setting up formulas for my POW Sheet .I would like to have my status column of an activity to be updated based on the actual start and End Date

Start Date = Blank Status to be updated as Not Started (Red)
Start Date is not Blank and End Date = Blank ; Status to be reported as In Progress (Amber)
Start Date= Not Blank and End Date = Not Blank ,Completed .(Green)

Appreciate assistance with the same

David Joyeuse · 09/19/20

Hi @Jameel Nahas

You could try this:

=IF(ISBLANK([Start Date]@row), "Red", IF(ISBLANK([End Date]@row), "Amber", "Green"))

Hope it helped!

Andrée Starå · 09/25/20

Hi @Jameel Nahas

To add to David's excellent answer.

Here is another version of the same formula.

=IF([Start Date]@row="", "Red", IF([End Date]@row="", "Amber", "Green"))

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jameel Nahas · 09/27/20

Hi Andree and David ,

Thanks for the response .Unfortunately this didnt work and has returned with the following error .What am i doing wrong here and it seems to be a formula /syntax error

I have attached a snapshot for the below

Andrée Starå · 09/27/20

@Jameel Nahas

Happy to help!

There seem to be multiple issues.

Try this.

=IF([Actual Start Date]row="","Red",IF([Actual End Date]@row="","Amber","Green")

Also, make sure that the Status Column is a Symbol Type column that matches your formula.

Make sense?

Did that work?

Jameel Nahas · 09/27/20

Hi Andrea ,

Appreciate your assistance and helping out

The solution didn't provide me the intended output in the below section .

If you review the below snapshot the last row shows me as the status to be "IN PROGRESS" ,.The activity should have status has Not Started as actual start date and end date columns have no date entry .