Conditional Formating: Changing Cell Color based on % completed when compaired to Timline

[vkazmierski]

Hello,

I have been refferencing this site for a while and finally had a question that I could not find the answer too, so this is my first post. I am not sure if it is possible or not, but I have havent been able to find exactly what I am looking for. I took over a spreadsheet that is tracking status of varrious projects and the cells are set to change to certin colors based on %. What I am looking to do is essentially the same thing, but take it to the next level, I want my it to look at the percentage complete, compare it to the current date and the created timeline, and change its color to show its status.

For example, I want it to display Green for completed phases at 100%.

But for phases that are not 100% I want it to look at the % and then look at the period in the timeline, for example a 60 day window (2/15-4/15) and know that if it is 50% done on 3/15 we are on schedule and it is yellow, however if it is 20% done on 4/1, or past the due date, we are behind and it should be red.

What I am hoping for it to do is to recognize what % we should be at as per the timeline, and compare it to the actual % and report if we are where we should be or in danger of falling behind.

I hope I did not confuse things to much. Is this possible, and if so how?

Thank you for the support,

-V

[NBVC]

What kind of conditional formulas do you have in there now? Where is it getting the info to determine the calculations it needs?

[vkazmierski]

It is currently being formated based on cell value, whatever % is entered into the cell. That is the first part I am strugling with, I have timelines, for each project, so in another tab within the spreadsheet, I have a start and end date for each phase. If possible, I need the cell to figure out how far along we should be % wise, and then use the current date to determine which color the cell displays.

[NBVC]

To get percentage complete:

=MAX(0,(TODAY()-Start)/(End-Start))

You can use that to get your conditional formats and colours.

[vkazmierski]

I will certainly try that out, thanks for your help, I will let you know how I make out.