Automatically assigning no symbol if finish date is blank

Peter Mc Kenna

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

Ramzi K

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

Peter Mc Kenna

Hi Ramzi,

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

Regards,

Peter

janiebn

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