Conditional formatting for active projects in Gantt chart

Caleb W

I am wondering if there is a way to set a condition that if there is a project/task based on "today" that it could highlight the row?

Also what is the best way to set up a resource management for that same gantt to let me see which of my superintendants are busy as to night over booked when I assign them to the task.

Andrée Starå

Hi @Caleb W

I hope you're well and safe!

For your first question.

You can use something like this in a so-called helper column (Checkbox) or add something like the second screenshot directly in Conditional Formatting.

=IF(Date@row = TODAY(), 1)

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

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

Caleb W

Thank you Andree! Is there a way where I can have it exclude the parent rows of eat task?

Caleb W

Another thing after looking at this. My gantt schedule has a start and finish date columns, how can I use the if today formula to capture the date range of the start and end?

Nick Korna

Something along the lines of:

=IF(AND([Start Date]@row <= TODAY(), [Finish Date]@row >= TODAY()), 1)

This would see if your start date is in the past/today and your finish date is in the future/today.

Then have your conditional formatting look at your helper column:

Selecting colour/task bar as required.

You could do accomplish the same with a few conditional formatting rules and no helper column if you wanted further distinction to starting/finishing/ongoing:

Obviously if you have no same day start/finishes then you can get rid of the final option.

Hope this helps, but any problems/questions then just post!

Caleb W

@Nick Korna ,

Thank you as that formula works out!! Is there a way to exclude parent rows in that formula?

Nick Korna

Something like this?

=IF(AND([Start Date]@row <= TODAY(), [Finish Date]@row >= TODAY(), COUNT(PARENT([Start Date]@row)) > 0), 1)

Caleb W

@Nick Korna there are still a few parent rows that mark as "onsite" becasue the jobs does stretch out but I have no subtasks this week? The blue row is my parent row, rows under are the subtasks.

Nick Korna

This is because the date range is included and there isn't a parent above that - to exclude rows like this amend to:

=IF(AND([Start Date]@row <= TODAY(), [Finish Date]@row >= TODAY(), COUNT(PARENT([Primary Column]@row)) > 0), 1, IF(COUNTIF(CHILDREN(), 1) >= 1, 1))

Examples:

Hopefully that should resolve it - if you still have issues then let me know. 🙂

Caleb W

@Nick Korna ,

Thank you so much for the help as that worked our perfect!!

I do have one more questions for you. In a similar scenario, I have a Flag to assign a task. I am trying to get the check box at parent row "ready for PCI audit" , in this case "ancestors 1" to check at% complete = >50%

Nick Korna

If it's only the parent lines that need flagging then you can use this:

=IFERROR(IF(AVG(CHILDREN([% complete]@row)) >= 0.5, 1, 0), "")

If you need the children to also to be able to be flagged you'd need this to be a cell formula rather than a column one.

Alternatively you could set up a helper column to recognise which rows are parent rows:

=IF(COUNT(CHILDREN([% complete]@row)) > 0, 1)

and then use an automation workflow to tick the box once a parent row (i.e. that column has a 1 in it) and the % complete hits = > 50% then check the box. The only caveat with this method is that for some reason it only seems to work with a checkbox - not sure why it doesn't allow a flag or star instead.

Let me know if this works for you, and if there is anything else. 😉