Percent Complete Versus Start and End Date Formulas For Conditional Formatting

[Consult123]

Hello all,

I'm having trouble with conditional formatting formulas for the following percent complete versus start and end date situations. I've also included a screenshot to give you a better picture.
-On Track (Trigger- Based on the current date, the task will be completed on time by the due date)
-Complete (Trigger- Column F at 100%)
-At Risk (Trigger- Based on the current date (halfway through duration)and percent complete, the task will not be completed on time by the due date)
-Off Track (Trigger- Based on the current date (latter half of duration) and percent complete, the task will not be completed on time by the due date)
-In Progress (Trigger- Column L >1% for the first seven days from the start date)
-Not Started (Trigger- Column F at 0%)
-Cancelled (Trigger- Columns D and E not populated with date and Column L blank)

 

[awoohaw]

well, you've posted what formulas you have. There is a testing area for working with xl2bb, see that link below as well. I'm not sure how the debugging works.
If you can change your TRUST and ADD IN setting in your OPTIONS page you may be able to get that to work. It comes in VERY Handy and you get much quicker responses from the forum when you use it.

The main concern at this point with your workbook is your ambiguity in definitions.

 

[Consult123]

in your original post you have a record that is "AT RISK" that is 51% complete. According to your definition above that should be "OFF TRACK".
What is your definition of "DURATION", My guess is Current Date - Start Date.
What about EXACTLY 50% of duration, is that AT RISK or OFF TRACK, when progress is less than 50%?

What if progress is 75% but duration has elapsed 70%? Is that "ON TRACK", or is it "IN PROGRESS", there seems to be ambiguity there as well?


A suggestion for your formula in column L is:

=IF(D3="","",IF(D3>Today(),0,Today()-D3+1)/(Today()-D3+1)))

in your original post you have a record that is "AT RISK" that is 51% complete. No, less that 50% of the duration has elapsed but you are still not aligning with column L would be AT RISK. The logic is this, you're still early enough that you can self correct and complete ontime. According to your definition above that should be "OFF TRACK". More that 50% of the duration has elapsed but you are still not aligning with column L would be OFF TRACK. The logic is this, you're now late in catching the variance so it will be tougher to get back back on track and complete on time.
What is your definition of "DURATION", My guess is Current Date - Start Date. Yes, you are correct. Sorry for not explaining that better.
What about EXACTLY 50% of duration, is that AT RISK or OFF TRACK, when progress is less than 50%? No, less than or equal to 50% would be the "AT RISK" and greater than or equal to 51% would be the "OFF TRACK" aka train is detrailing (LOL) category. Does this make sense?

I do a lot of construction scheduling. Projects are hard to get back in line after the 30% or 50% time spent duration becuase you have less time to do so and have usually exhausted most of your finances in early efforts. I hope this makes sense.

 

[Consult123]

well, you've posted what formulas you have. There is a testing area for working with xl2bb, see that link below as well. I'm not sure how the debugging works.
If you can change your TRUST and ADD IN setting in your OPTIONS page you may be able to get that to work. It comes in VERY Handy and you get much quicker responses from the forum when you use it.

The main concern at this point with your workbook is your ambiguity in definitions.

Goals List Creation.xlsx
	A	B		D	E	F	G					L
3	Task ID	Task Name		Start Date	Due Date	Actual % Complete	Status					Progress Tracker
4	TASK-001	Support new user acquisition in targeted regions		12/29/23	12/1/2024	1%	IN PROGRESS					1%
5	TASK-002	Improve customer satisfaction and reduce churn		10/02/23	1/2/2024	5%	OFF TRACK					97%
6	TASK-003	Bolster revenue from new products and services		12/03/23	12/3/2024	1%	ON TRACK					8%
7	TASK-004	Increase upsell and cross-sell revenue from existing customers		01/04/23	12/4/2024	50%	AT RISK					51%
8	Q2	2024
9	Task ID	Task Name		Start Date	Due Date		Status					Progress Tracker
10	TASK-004	Ramp up talent acquisition to strengthen teams		12/30/2023	12/30/2023	100%	COMPLETE					100%
11	TASK-005	Cultivate a customer community					CANCELLED
12	TASK-006	Increase employee productivity through automation		1/3/2024	12/3/2024	0%	NOT STARTED					0%
2024

Cell Formulas
Range	Formula
L12,L10,L4:L7	L4	=IFERROR(MIN((DATEDIF(D4,TODAY(),"d")+1)/(DATEDIF(D4,E4,"d")+1),100%),0)

I answered my ambiguous definitions in an earlier comment to you. Thanks again for your help!

 

[Consult123]

You should put formulas in column G, then use conditional formatting to apply colors based on what words are generated by the formulas.

How are you determining, based on the current date, whether a task will be completed by the due date? Are you calculating whether percent of elapsed time is greater than or less than "actual % complete"? How is "Actual % Complete" entered--is it a formula, or entered manually based on some objective measure of completion?

% complete is a notoriously unreliable way to measure task progress. (Cite: 35 years of IT project management experience)

You should put formulas in column G, then use conditional formatting to apply colors based on what words are generated by the formulas. Agreed, but no one has provided formulas. My screen shot is an example of the end product, minus the formulas.

How are you determining, based on the current date, whether a task will be completed by the due date? Are you calculating whether percent of elapsed time is greater than or less than "actual % complete"? How is "Actual % Complete" entered--is it a formula, or entered manually based on some objective measure of completion? Manual input based on percieved complete or logic based decision. Also, see below comment from previous thread.

% complete is a notoriously unreliable way to measure task progress. (Cite: 35 years of IT project management experience) Agreed, but this is for a personal and life goal list which is high level and only scrutinized by me, myself, and I. I don't want to track units becuase some task % completes will be observation based. For instance, studying for a new certification...If I feel I'm about 1/3 of the way through that task I would hard key 30%. I am the only one reviewing the results and I shouldn't be putting in false percent complete numbers. There's less accountability here and only myself to blame if I put in wrong percentages. Does this make sense?
in your original post you have a record that is "AT RISK" that is 51% complete. No, less that 50% of the duration has elapsed but you are still not aligning with column L would be AT RISK. The logic is this, you're still early enough that you can self correct and complete ontime. According to your definition above that should be "OFF TRACK". More that 50% of the duration has elapsed but you are still not aligning with column L would be OFF TRACK. The logic is this, you're now late in catching the variance so it will be tougher to get back back on track and complete on time.
What is your definition of "DURATION", My guess is Current Date - Start Date. Yes, you are correct. Sorry for not explaining that better.
What about EXACTLY 50% of duration, is that AT RISK or OFF TRACK, when progress is less than 50%? No, less than or equal to 50% would be the "AT RISK" and greater than or equal to 51% would be the "OFF TRACK" aka train is detrailing (LOL) category. Does this make sense?

I do a lot of construction scheduling. Projects are hard to get back in line after the 30% or 50% time spent duration becuase you have less time to do so and have usually exhausted most of your finances in early efforts. I hope this makes sense.

 

[awoohaw]

The forum is here to help you, not get into arguments with you. If someone says they don't understand or something is ambiguous it is up to you to help them figure why it is not ambiguous, or provide a correction.

Here are your orginal "status" definitions, and any questions I have or updates that you have since answered. If you can't get all of the questions resolved you will not get your conditional formatting rules based on formulas (where as we have already provided CF based on values)

And as far as a formula in column L, a formula was given in post #9. Was that adequate? if it wrong please update with why/how you think it is wrong.


On Track (Trigger- Based on the current date, the task will be completed on time by the due date)
This is vague, according to this statement, any date between the start and end would be "ON TRACK", Is that accurate? Or, would a better definition of this be Actual % Completed >= Progress Tracker %? How does this differ from "IN PROGRESS"?

-Complete (Trigger- Column F at 100%)

-At Risk (Trigger- Based on the current date (halfway through duration)and percent complete, the task will not be completed on time by the due date)
My translation of what you have replied with earlier:
Two Conditions:
Actual Percent < Progress Tracker %,
AND
Progress Tracker is Less Than or Equal to 50%

-Off Track (Trigger- Based on the current date (latter half of duration) and percent complete, the task will not be completed on time by the due date)
My translation of what you have replied with earlier:
Two Conditions:
Actual Percent < Progress Tracker %,
AND
Progress Tracker is Greater Than 50%


-In Progress (Trigger- Column L >1% for the first seven days from the start date)
What if End Date is Less than 7 Days? What status do you want when Actual Status is 5% on Day 3, or 25% on Day 6? (Wouldn't you want "AT RISK", and "OFF TRACK" respectively?) What is the difference between "ON TARGET" and "IN PROGRESS"

-Not Started (Trigger- Column F at 0%)

-Cancelled (Trigger- Columns D and E not populated with date and Column L blank)
You probably do not need to have a test for column L being blank. Of course maybe i don't fully understand this part of the CF.
My suggestion for this would be to just have CF on Columns D & E that they are either both populated or both blank.

 

[Consult123]

in your original post you have a record that is "AT RISK" that is 51% complete. According to your definition above that should be "OFF TRACK".
What is your definition of "DURATION", My guess is Current Date - Start Date.
What about EXACTLY 50% of duration, is that AT RISK or OFF TRACK, when progress is less than 50%?

What if progress is 75% but duration has elapsed 70%? Is that "ON TRACK", or is it "IN PROGRESS", there seems to be ambiguity there as well?


A suggestion for your formula in column L is:

=IF(D3="","",IF(D3>Today(),0,Today()-D3+1)/(Today()-D3+1)))

=IF(D3="","",IF(D3>Today(),0,Today()-D3+1)/(Today()-D3+1))) code should start at D4 and include E4 because that is the "timeline to complete," correct? Your

The forum is here to help you, not get into arguments with you. If someone says they don't understand or something is ambiguous it is up to you to help them figure why it is not ambiguous, or provide a correction.

Here are your orginal "status" definitions, and any questions I have or updates that you have since answered. If you can't get all of the questions resolved you will not get your conditional formatting rules based on formulas (where as we have already provided CF based on values)

And as far as a formula in column L, a formula was given in post #9. Was that adequate? if it wrong please update with why/how you think it is wrong.


On Track (Trigger- Based on the current date, the task will be completed on time by the due date)
This is vague, according to this statement, any date between the start and end would be "ON TRACK", Is that accurate? Or, would a better definition of this be Actual % Completed >= Progress Tracker %? How does this differ from "IN PROGRESS"?

-Complete (Trigger- Column F at 100%)

-At Risk (Trigger- Based on the current date (halfway through duration)and percent complete, the task will not be completed on time by the due date)
My translation of what you have replied with earlier:
Two Conditions:
Actual Percent < Progress Tracker %,
AND
Progress Tracker is Less Than or Equal to 50%

-Off Track (Trigger- Based on the current date (latter half of duration) and percent complete, the task will not be completed on time by the due date)
My translation of what you have replied with earlier:
Two Conditions:
Actual Percent < Progress Tracker %,
AND
Progress Tracker is Greater Than 50%


-In Progress (Trigger- Column L >1% for the first seven days from the start date)
What if End Date is Less than 7 Days? What status do you want when Actual Status is 5% on Day 3, or 25% on Day 6? (Wouldn't you want "AT RISK", and "OFF TRACK" respectively?) What is the difference between "ON TARGET" and "IN PROGRESS"

-Not Started (Trigger- Column F at 0%)

-Cancelled (Trigger- Columns D and E not populated with date and Column L blank)
You probably do not need to have a test for column L being blank. Of course maybe i don't fully understand this part of the CF.
My suggestion for this would be to just have CF on Columns D & E that they are either both populated or both blank.

@awoohaw Here is a better version I just created (see below). I have conditional formatting fixed, but I'm having trouble tweaking the formula. See the table on the top right for requirements clarity.
I truly hope my hard work got us moving in the right direction here...

Goals List Creation Rev1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Q1	2024
2	Task ID	Task Name	Start Date	Due Date	Act. % Progress	Completed Date	Status	Days Remaining		Status List	Descriptions
3	TASK-001	Put laundry in hamper	12/29/23	12/1/2024	100%	12/1/2024	#N/A			LATE	Past expected completion date
4	TASK-002	Put laundry in washer	10/02/23	1/2/2024	21%		COMPLETED	92		AT RISK	7 or more days behind schedule
5	TASK-003	Add detergent	12/21/23	12/20/2023	25%		AT RISK	-1		ON SCHEDULE	Meeting schedule day for day
6	TASK-004	Start laundry	01/04/23	12/4/2024	45%		COMPLETED	700		AHEAD OF SCHEDULE	Ahead of schedule by one day or more
7	Q2	2024								COMPLETE	Date entered into Column G
8	Task ID	Task Name	Start Date	Due Date			Status			NOT STARTED	No input in Column E
9	TASK-004	Move laundry to dryer	12/30/2023	12/22/2023			LATE	-8
10	TASK-005	Add dryer sheets	12/30/2023	12/14/2023			LATE	-16
11	TASK-006	Start dryer	1/3/2024	12/3/2024			COMPLETED	335
12	Q3	2024
13	Task ID	Task Name	Start Date	Due Date			Status
14	TASK-007	Remove laundry from dryer	1/1/2024	1/1/2024			ON SCHEDULE	0
15	TASK-008	Clean dryer filter	1/2/2024	1/3/2024			AHEAD OF SCHEDULE	1
16	TASK-009	Fold laundry	1/3/2024	1/3/2024			ON SCHEDULE	0
17	Q4	2024
18	Task ID	Task Name	Start Date	Due Date			Status
19	TASK-010	Hang laundry	1/1/2024	1/1/2024			ON SCHEDULE	0
20	TASK-011	Iron laundry	1/2/2024	1/2/2024			ON SCHEDULE	0
21	TASK-012	Inspect laundry	1/3/2024	1/3/2024			ON SCHEDULE	0
2024

Cell Formulas
Range	Formula
G19:G21,G14:G16,G9:G11,G3:G6	G3	=IF(H3="","",LOOKUP(H3,{-999,-1,0,1,10},{"LATE","AT RISK","ON SCHEDULE","AHEAD OF SCHEDULE","COMPLETED"}))
H19:H21,H14:H16,H9:H11,H4:H6	H4	=DAYS(D4,C4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G59	Cell Value	="NOT STARTED"	text	NO
G2:G59	Cell Value	="AHEAD of SCHEDULE"	text	NO
G2:G59	Cell Value	="COMPLETED"	text	NO
G3:G6	Cell Value	contains "COMPLETED"	text	NO
G2:G59	Cell Value	contains ""	text	NO
G2:G59	Cell Value	contains ""	text	NO
J8	Cell Value	="AT RISK"	text	NO
G2:G59	Cell Value	contains ""	text	NO

 

[Consult123]

Updated spreadsheet below; seeking to make inclusive formula for column G that accounts for columns C,D,E,F, and H. I've done most of the hard work, now I need an excel wizard to help with the rest.

Goals List Creation Rev1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Q1	2024
2	Task ID	Task Name	Start Date	Due Date	Act. % Progress	Completed Date	Status	Days Remaining		Status List	Descriptions
3	TASK-001	Put laundry in hamper	12/29/23	12/1/2024	100%	12/1/2024	#N/A			LATE	Past expected completion date
4	TASK-002	Put laundry in washer	10/02/23	1/2/2024	21%		COMPLETED	92		AT RISK	7 or more days behind schedule
5	TASK-003	Add detergent	12/21/23	12/20/2023	25%		AT RISK	-1		ON SCHEDULE	Meeting schedule day for day
6	TASK-004	Start laundry	01/04/23	12/4/2024	45%		COMPLETED	700		AHEAD OF SCHEDULE	Ahead of schedule by one day or more
7	Q2	2024								COMPLETE	Date entered into Column G
8	Task ID	Task Name	Start Date	Due Date			Status			NOT STARTED	No input in Column E
9	TASK-004	Move laundry to dryer	12/30/2023	12/22/2023			LATE	-8
10	TASK-005	Add dryer sheets	12/30/2023	12/14/2023			LATE	-16
11	TASK-006	Start dryer	1/3/2024	12/3/2024			COMPLETED	335
12	Q3	2024
13	Task ID	Task Name	Start Date	Due Date			Status
14	TASK-007	Remove laundry from dryer	1/1/2024	1/1/2024			ON SCHEDULE	0
15	TASK-008	Clean dryer filter	1/2/2024	1/3/2024			AHEAD OF SCHEDULE	1
16	TASK-009	Fold laundry	1/3/2024	1/3/2024			ON SCHEDULE	0
17	Q4	2024
18	Task ID	Task Name	Start Date	Due Date			Status
19	TASK-010	Hang laundry	1/1/2024	1/1/2024			ON SCHEDULE	0
20	TASK-011	Iron laundry	1/2/2024	1/2/2024			ON SCHEDULE	0
21	TASK-012	Inspect laundry	1/3/2024	1/3/2024			ON SCHEDULE	0
2024

Cell Formulas
Range	Formula
G19:G21,G14:G16,G9:G11,G3:G6	G3	=IF(H3="","",LOOKUP(H3,{-999,-1,0,1,10},{"LATE","AT RISK","ON SCHEDULE","AHEAD OF SCHEDULE","COMPLETED"}))
H19:H21,H14:H16,H9:H11,H4:H6	H4	=DAYS(D4,C4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G59	Cell Value	="NOT STARTED"	text	NO
G2:G59	Cell Value	="AHEAD of SCHEDULE"	text	NO
G2:G59	Cell Value	="COMPLETED"	text	NO
G3:G6	Cell Value	contains "COMPLETED"	text	NO
G2:G59	Cell Value	contains ""	text	NO
G2:G59	Cell Value	contains ""	text	NO
J8	Cell Value	="AT RISK"	text	NO
G2:G59	Cell Value	contains ""	text	NO

 

[awoohaw]

thanks for getting the xl2bb working!
What is you you have not accomplished here? I'm confused. It looks like you have it all. You're calculating your statuses and have it conditionally formatted? Never mind. You want to combine all into one formula. Okay. Will see what I can do. But your column references seem off, G should be F? All offset by 1 column somehow?

 

[Consult123]

thanks for getting the xl2bb working!
What is you you have not accomplished here? I'm confused. It looks like you have it all. You're calculating your statuses and have it conditionally formatted? Never mind. You want to combine all into one formula. Okay. Will see what I can do. But your column references seem off, G should be F? All offset by 1 column somehow?

No, Colum F and G are hard key inputs.

Here's a link to potentially help Earned Schedule Management (ESM) - MPUG
Here's what we are tying to achieve:

Known issues:
1. When a date is placed in column F, column H should say "Completed"
2. When no data is in column E, column H should say "Not Started"
3. All other column H conditional formatting from key table to top right not calculating properly.

Goals List Creation Rev1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Q1	2024
2	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)		Status List	Descriptions
3	TASK-001	Put laundry in hamper	1/1/2024	1/1/2024	100%	1/1/2024	0	ON SCHEDULE		LATE	>completion date
4	TASK-002	Put laundry in washer	10/02/23	1/2/2024	21%		92	COMPLETED		AT RISK	³7 days behind schedule
5	TASK-003	Add detergent	12/21/23	12/20/2023	25%		-1	AT RISK		ON SCHEDULE	Meeting schedule day for day
6	TASK-004	Start laundry	01/04/23	12/4/2024	45%		700	COMPLETED		AHEAD OF SCHEDULE	Ahead of schedule by one day or more
7	Q2	2024								COMPLETE	Date entered into Column G
8	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)		NOT STARTED	No input in Column E
9	TASK-004	Move laundry to dryer	12/30/2023	12/22/2023			-8	LATE
10	TASK-005	Add dryer sheets	12/30/2023	12/14/2023			-16	LATE
11	TASK-006	Start dryer	1/3/2024	12/3/2024			335	COMPLETED
12	Q3	2024
13	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)
14	TASK-007	Remove laundry from dryer					0	ON SCHEDULE
15	TASK-008	Clean dryer filter					0	ON SCHEDULE
16	TASK-009	Fold laundry					0	ON SCHEDULE
17	Q4	2024
18	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)
19	TASK-010	Hang laundry					0	ON SCHEDULE
20	TASK-011	Iron laundry					0	ON SCHEDULE
21	TASK-012	Inspect laundry					0	ON SCHEDULE
2024

Cell Formulas
Range	Formula
G19:G21,G14:G16,G9:G11,G3:G6	G3	=DAYS(D3,C3)
H19:H21,H14:H16,H9:H11,H3:H6	H3	=IF(G3="","",LOOKUP(G3,{-999,-1,0,1,10},{"LATE","AT RISK","ON SCHEDULE","AHEAD OF SCHEDULE","COMPLETED"}))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:H59	Cell Value	="NOT STARTED"	text	NO
H2:H59	Cell Value	="AHEAD of SCHEDULE"	text	NO
H2:H59	Cell Value	="COMPLETED"	text	NO
H3:H6	Cell Value	contains "COMPLETED"	text	NO
H2:H59	Cell Value	contains ""	text	NO
H2:H59	Cell Value	contains ""	text	NO
J8	Cell Value	="AT RISK"	text	NO
H2:H59	Cell Value	contains ""	text	NO

 

[awoohaw]

No, Colum F and G are hard key inputs.

Here's a link to potentially help Earned Schedule Management (ESM) - MPUG
Here's what we are tying to achieve:
View attachment 104139





Known issues:
1. When a date is placed in column F, column H should say "Completed"
2. When no data is in column E, column H should say "Not Started"
3. All other column H conditional formatting from key table to top right not calculating properly.

Goals List Creation Rev1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Q1	2024
2	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)		Status List	Descriptions
3	TASK-001	Put laundry in hamper	1/1/2024	1/1/2024	100%	1/1/2024	0	ON SCHEDULE		LATE	>completion date
4	TASK-002	Put laundry in washer	10/02/23	1/2/2024	21%		92	COMPLETED		AT RISK	³7 days behind schedule
5	TASK-003	Add detergent	12/21/23	12/20/2023	25%		-1	AT RISK		ON SCHEDULE	Meeting schedule day for day
6	TASK-004	Start laundry	01/04/23	12/4/2024	45%		700	COMPLETED		AHEAD OF SCHEDULE	Ahead of schedule by one day or more
7	Q2	2024								COMPLETE	Date entered into Column G
8	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)		NOT STARTED	No input in Column E
9	TASK-004	Move laundry to dryer	12/30/2023	12/22/2023			-8	LATE
10	TASK-005	Add dryer sheets	12/30/2023	12/14/2023			-16	LATE
11	TASK-006	Start dryer	1/3/2024	12/3/2024			335	COMPLETED
12	Q3	2024
13	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)
14	TASK-007	Remove laundry from dryer					0	ON SCHEDULE
15	TASK-008	Clean dryer filter					0	ON SCHEDULE
16	TASK-009	Fold laundry					0	ON SCHEDULE
17	Q4	2024
18	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)
19	TASK-010	Hang laundry					0	ON SCHEDULE
20	TASK-011	Iron laundry					0	ON SCHEDULE
21	TASK-012	Inspect laundry					0	ON SCHEDULE
2024

Cell Formulas
Range	Formula
G19:G21,G14:G16,G9:G11,G3:G6	G3	=DAYS(D3,C3)
H19:H21,H14:H16,H9:H11,H3:H6	H3	=IF(G3="","",LOOKUP(G3,{-999,-1,0,1,10},{"LATE","AT RISK","ON SCHEDULE","AHEAD OF SCHEDULE","COMPLETED"}))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:H59	Cell Value	="NOT STARTED"	text	NO
H2:H59	Cell Value	="AHEAD of SCHEDULE"	text	NO
H2:H59	Cell Value	="COMPLETED"	text	NO
H3:H6	Cell Value	contains "COMPLETED"	text	NO
H2:H59	Cell Value	contains ""	text	NO
H2:H59	Cell Value	contains ""	text	NO
J8	Cell Value	="AT RISK"	text	NO
H2:H59	Cell Value	contains ""	text	NO

Well, everytime you post a new chart the reference column changes. Its late, I'll have to look more at this tomorrow. Of course other forum folks will chime in if they are interested.