project tracking sheet with dates and conditional formatting.

I am working on a project timeline document.

I want each phase of the project to have a different color.
When I have a new project I would input the data into excel along with a column for the current date. Lets say the project "start" phase is blue. When the project moves into another phase, lets say "red" color, I would like the date to update to the current days date when the phase color was changed.

The first part of this is to be able to reference when the project started the new phase.
The second part is to have another table that is tracking each phase change or each project in order to look back and see the dates when a specific project changed phases.

Any help with this would be appreciated.

Thank you.

Your best bet is to develop a file that shows the data (project name, dates, phases, etc.) the way you want it to look then we can help with the colors and dates.

One thing I don't understand from your description. "When the project moves into another phase[...]I would like the date to update to the current days date" If you want the date to automatically change, how does Excel know when the phase changed? I would expect that you would have a table with the phases and each time you enter a new phase, you enter that date. It seems like you want the spreadsheet to just know when the phase changes.

Hi 6StringJazzer,

Thank you for your reply. I have attached an excel document that I hope will clear things up.

Basically I am trying to do the following: In the timeline sheet, when I input a project, each project could be in a different phase at any time. If today I set a project phase to 'site-visit' I want today's date 11/09/20 to appear in the phase date column. Tomorrow if I change the phase to 'cancelled' I want the phase date column to update to tomorrow's date 11/10/20.

In the Consolidated Date sheet, I want to track each phases date ignorer to look back at the total timeline for the project.

I hope this helps. Please take a look at the file, I think it will clear up any in consistencies in my description.

Thank you.

Recording a date when a field changes and making it stick generally requires VBA. I have added the VBA to do that.

The way you have your data layout makes completing the second sheet with formulas complicated so I will also look at a VBA solution for that.

Thank you for sending this along. This is exactly what I am trying to do. Thank you for your help.

I will await your thoughts on the second part. Thank you for looking into that as well!

Here is a solution that requires a helper column in each sheet to do a lookup of the dates. That allows it to be done with a simple formula.

What is your Excel version? There is no version 2020. The latest versions are 2019 and Office 365. I am thinking that the formula may need to be entered as an array formula on earlier versions.