Automatically assigning no symbol if finish date is blank

Options

HI,

I am assigning Blue(Complete), Green(On Track), Yellow(Due in next 5 days), & Red (Over due) symbols to each task in a product plan in order to create a task status report. Becase I have the red symbol set as finish date in the past, all tasks with no finish date are showing up red. Can yuo help me with my formula below to add a function that will leave the symbols cell blank if the finish date is blank?


=IF([% Complete]56 = 1, "Blue", IF(Finish56 < TODAY(), "Red", IF(Finish56 <= TODAY() + 5, "Yellow", IF(Finish56 > TODAY(), "Green"))))


regards,

Peter

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Options

    Hi @Peter Mc Kenna,

    Try using the ISBLANK function like this:

    =IF(ISBLANK([Date Field]@row), "", =IF([% Complete]56 = 1, "Blue", IF(Finish56 < TODAY(), "Red", IF(Finish56 <= TODAY() + 5, "Yellow", IF(Finish56 > TODAY(), "Green")))))

    I hope this helps.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Peter Mc Kenna
    Options

    Hi Ramzi,


    Thats great. Worked perfectly. Thank you for your help.


    Regards,

    Peter

  • janiebn
    Options

    I'm trying to do something similar but not sure how (sorry if my formula is in french)

    =IF([État de la tâche]1 = "Complété"; "Vert"; IF(AND([État de la tâche]1 <> "Complété"; TODAY() > [Date de fin]1); "Rouge"; IF(AND([État de la tâche]1 = "Pas Commencé"; "Bleu"; IF(AND([État de la tâche]1 = "En cours"; "Jaune"))))))

    This formula basicaly says if in cell 17 in a certain column i have selected either complete, not started or in progess from a dropdown menu then show a certain color symbol and if the task is not complete and the date is overdue then show a red symbol. What I would like to add to this formula if the cell is empty (didn't select anything from the dropdown) or if the task has no end date then leave the cell, where i ususally see a color symbol, blank. Right now i get a red symbol because i don't have an end date to the task

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!