Logic For a Gantt Chart

[LJSHawk]

Right now I am working with a project management in Excel. On the left, I have a column for the name of the task, a column for the start date, a column for the end data, and a column for the status of the project (Completed, In progress, overdue, etc).

To the right of all of these columns I have a Gantt chart with all the days of the year. I am using logic in these cells so that if the dates are between the start dates and end dates of the project, the cells are given a "A." If not, the cells are given a "0." I am then using conditional formatting to give the cells different colors based on the dates.

Basically, my problem is that I need to use some type of different logic in the Gantt chart so that when the project status changes to "overdue," only the cells between the start date and end date need to change to a different color (i.e. red). So right now, my logic is: =IF(AND("Date in Gantt Chart">="task start date", "Date in Gantt Chart"<="task end date"), "A", "0"). I am then using conditional formatting to highlight those cells. I just need to have those same cells be able to take on a different value when the status in the corresponding row is changed to "overdue."

Any ideas?

 

[xlHammer]

May be something like this,

=IF(AND("Date in Gantt Chart">="task start date", "Date in Gantt Chart"<="task end date"),IF("status"="overdue","B", "A"), "0")

 

[LJSHawk]

Yes, that worked perfectly. Thanks so much.