Conditional Formatting Issue

[CY078]

Hi All

I am having issues with conditional formatting formulas. The spreadsheet is a "project timeline" with the formatting rules set out in the pink box below.

My issue is with the "red highlight" ... it should be showing if the "progress < 100%" and the "end date < today" then it should highlight red (being that it is behind schedule).

But it still stays green with anything less than the start date being red.

Can anyone please advise what have I done wrong here ?

Thanks

 

[6StringJazzer]

At first glance the red rule applies only to the cells to the left of the start date. I am assuming that your Applies To range starts in column K (see #1 below). Also I am thinking you also want columns to be red that are before today but after the scheduled end date. You haven't really spelled that out. I am thinking the rule should be

=AND(task_progress<1,task_start<=K$9,task_end<=TODAY(),K$9<=TODAY())

But that is an educated guess.

You are not showing us the whole picture.

1. You are not showing us the Applies To ranges for your CF rules.
2. We can see your Start and End dates on the screenshot, but not the % complete numbers.
3. Your formula references K$9 but you are not showing us that column. If you are just hiding past dates that's OK, but if your Applies To range doesn't start in K this won't work (see #1)
4. We need to know how named ranges task_progress, task_start, and task_end are defined

 

[CY078]

At first glance the red rule applies only to the cells to the left of the start date. I am assuming that your Applies To range starts in column K (see #1 below). Also I am thinking you also want columns to be red that are before today but after the scheduled end date. You haven't really spelled that out. I am thinking the rule should be

=AND(task_progress<1,task_start<=K$9,task_end<=TODAY(),K$9<=TODAY())

But that is an educated guess.

You are not showing us the whole picture.

1. You are not showing us the Applies To ranges for your CF rules.
2. We can see your Start and End dates on the screenshot, but not the % complete numbers.
3. Your formula references K$9 but you are not showing us that column. If you are just hiding past dates that's OK, but if your Applies To range doesn't start in K this won't work (see #1)
4. We need to know how named ranges task_progress, task_start, and task_end are defined

#1: I thought the formula shown in the condition was just always the first one entered then its application to the selected cells. So in this case the rule manager shows K$9 but the formula applies to FC$9 (which is a date)

Apologies for point #2

I'm looking into point 4 atm as that is an area I did not inspect. I'll let you know what I find here.

Its probably easier to share the skeleton of the project timeline as per below.

Loading Google Sheets

Thanks

 

[CY078]

So i've looked at the name ranges and there is nothing conspicuous there. Some of them are not required so ended up deleting a couple.

I tried many a formula but could not get what I wanted.

The below pic is what I want as an example
- Row 23: The overall project is still ok but only 10% (1.2%) has been completed from the overall 12 days
- Row 24: There should be a red highlight on Tue 17th as there was an estimated 3 days to complete the task but only 2 days has been complete.

I can't seem to get the formula that says

IF(Task_Progress<100%),IF(Today()>task_start&task_end) then make it red ... I know I am tripping up with this part "IF(Today()>task_start&task_end)" but no matter how many different variations I try its not working.

Hopefully someone can guide me in the right direction.

Thanks

 

[6StringJazzer]

#1: I thought the formula shown in the condition was just always the first one entered then its application to the selected cells. So in this case the rule manager shows K$9 but the formula applies to FC$9 (which is a date)

You are correct and is in fact my whole point. The formula will apply to FC$9 if and only if the Applies To range for the rule starts in column K. But you didn't show the Applies To ranges in your screenshot (point #1) so we can't verify that's correct.

 

[6StringJazzer]

- Row 23: The overall project is still ok but only 10% (1.2%) has been completed from the overall 12 days
- Row 24: There should be a red highlight on Tue 17th as there was an estimated 3 days to complete the task but only 2 days has been complete.

The attached file is enormously helpful, thanks for providing that.

I am not clear on when you want red cells. At first I thought it was only for late projects past their End Date, but these two examples are a little different than that. Let's see if we can state the rule you want to use:

A cell is to be shown as red if the % complete shown in Progress is less than the elapsed number of days as a percentage of the total duration of the project shown in Days. Based on your image above saying this is what you want, only the cells that show progress-to-date will be red, with the remaining cells for the task up the End date to continue to be green.

The green color shows the days between the planned Start and End dates.

EDIT: I'm not sure what you're after with the gray cells

(By the way, I'm not seeing any way to show what happens if the actual start date of a task is not the Start date shown.)

 

[6StringJazzer]

Here is the rule as I described it, which does not exactly match your picture but it's an improvement over what you have.

=AND(task_progress<1,task_start<=K$9,K$9<=TODAY(),task_progress<(TODAY()-task_start)/(task_end-task_start))

 

[CY078]

Here is the rule as I described it, which does not exactly match your picture but it's an improvement over what you have.

=AND(task_progress<1,task_start<=K$9,K$9<=TODAY(),task_progress<(TODAY()-task_start)/(task_end-task_start))

View attachment 100575

This is working ... but the red cells do not finish at the end date if the progress is <1. It just keeps on going past it to Today(). I couldn't find a formula that would accommodate this request. Which led me to the below

A cell is to be shown as red if the % complete shown in Progress is less than the elapsed number of days as a percentage of the total duration of the project shown in Days.

The way you phrased this comment is good because it has prompted me to try and split (in a separate column) the progress in days. The thinking is trying to get the same outcome as above but with the Task_end involved. I'll let you know how I go.

EDIT: I'm not sure what you're after with the gray cells

The grey cells is just to signify that portion of the project is completed. Once 100% it should be fully greyed out.