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)

 

[6StringJazzer]

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)

 

[awoohaw]

I've tried to replicate your formula in column G based upon your prose description. I fail.
What is that formula. Using the xl2bb add in would be of great assistance. You'll get a much quicker answer.

you have ambiguity in the definition of at risk and off track. You have a value in the AT RISK Row that is greater than half way, so why isn't that row "OFF TRACK".
When you write halfway, that means Equal to 50%,


But to echo what @6StringJazzer wrote, here is CF based on your calculation results:

Book1
	D	E	F	G	H	I	J	K	L
1	TODAY:	2023-12-30
2	Start	Due	Act % Comp						Prog % Track
3	2023-12-29	2024-12-01	1.0%	IN PROGRESS					0.6%
4	2023-10-02	2024-01-02	5.0%	OFF TRACK					96.8%
5	2023-12-03	2024-12-03	1.0%	ON TRACK					7.6%
6	2023-01-04	2024-12-04	50.0%	AT RISK					51.5%
7
8	2023-12-30	2023-12-30	100.0%	COMPLETE					100.0%
9
10	2024-01-03	2024-12-03	0.0%	NOT STARTED					0.0%
Sheet3

Cell Formulas
Range	Formula
L3:L6,L8:L10	L3	=IF(D3="","",IF(D3>$E$1,0,($E$1-D3+1)/(E3-D3+1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:G10	Expression	=G3="CANCELLED"	text	NO
G3:G10	Expression	=G3="COMPLETE"	text	NO
G3:G10	Expression	=G3="AT RISK"	text	NO
G3:G10	Expression	=G3="ON TRACK"	text	NO
G3:G10	Expression	=G3="OFF TRACK"	text	NO
G3:G10	Expression	=G3="IN PROGRESS"	text	NO

 

[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)

The actual percent complete would be a hard key input from the user utilizing judgment and honesty. For example, Task-do all laundry / If you feel a certain percentage is complete, you will hard key that percentage into the cell.

I fully understand most "percent completes" are based on units or widgets (Earned Value) completed out of the total amount of units or widgets. However, for this "goals list" effort, I don't want to get into the weeds with units or widget counts.

 

[Consult123]

The actual percent complete would be a hard key input from the user utilizing judgment and honesty. For example, Task-do all laundry / If you feel a certain percentage is complete, you will hard key that percentage into the cell.

I fully understand most "percent completes" are based on units or widgets (Earned Value) completed out of the total amount of units or widgets. However, for this "goals list" effort, I don't want to get into the weeds with units or widget counts.

I also agree to put formulas in column G but left the status there so you'd better understand the goal. I hope this makes sense.

 

[awoohaw]

I also agree to put formulas in column G but left the status there so you'd better understand the goal. I hope this makes sense.

It will all make better sense if you define "AT RISK" and "OFF TRACK" unambiguously.
And provide an xl2bb or a line item of your formula for column G.

 

[Consult123]

I've tried to replicate your formula in column G based upon your prose description. I fail.
What is that formula. Using the xl2bb add in would be of great assistance. You'll get a much quicker answer.

you have ambiguity in the definition of at risk and off track. You have a value in the AT RISK Row that is greater than half way, so why isn't that row "OFF TRACK".
When you write halfway, that means Equal to 50%,


But to echo what @6StringJazzer wrote, here is CF based on your calculation results:

Book1
	D	E	F	G	H	I	J	K	L
1	TODAY:	2023-12-30
2	Start	Due	Act % Comp						Prog % Track
3	2023-12-29	2024-12-01	1.0%	IN PROGRESS					0.6%
4	2023-10-02	2024-01-02	5.0%	OFF TRACK					96.8%
5	2023-12-03	2024-12-03	1.0%	ON TRACK					7.6%
6	2023-01-04	2024-12-04	50.0%	AT RISK					51.5%
7
8	2023-12-30	2023-12-30	100.0%	COMPLETE					100.0%
9
10	2024-01-03	2024-12-03	0.0%	NOT STARTED					0.0%
Sheet3

Cell Formulas
Range	Formula
L3:L6,L8:L10	L3	=IF(D3="","",IF(D3>$E$1,0,($E$1-D3+1)/(E3-D3+1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:G10	Expression	=G3="CANCELLED"	text	NO
G3:G10	Expression	=G3="COMPLETE"	text	NO
G3:G10	Expression	=G3="AT RISK"	text	NO
G3:G10	Expression	=G3="ON TRACK"	text	NO
G3:G10	Expression	=G3="OFF TRACK"	text	NO
G3:G10	Expression	=G3="IN PROGRESS"	text	NO

I'm having a hard time understanding your response. Could you reiterate for a dumb person like me? Below is a copy of the formula from column L (no other columns currently have formulas). Please note my formula doesn't yet work for the situation in row 11 but I have another post regarding that fix so please disregard L11 issue.

Let me know if you want me to send my sheet over so you can play with it.

 

[Consult123]

It will all make better sense if you define "AT RISK" and "OFF TRACK" unambiguously.
And provide an xl2bb or a line item of your formula for column G.

-At Risk (Trigger- Based on the current date (<50% of duration)and percent complete, the task will not be completed on time by the due date)
-Off Track (Trigger- Based on the current date (>50% of duration) and percent complete, the task will not be completed on time by the due date)

I'm open to suggestions. Sorry if I'm not describing this in a computer or Excel language that is easy to understand.

 

[awoohaw]

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)))

 

[Consult123]

It will all make better sense if you define "AT RISK" and "OFF TRACK" unambiguously.
And provide an xl2bb or a line item of your formula for column G.

When I try to open XL2BB it says excel is in protected view. I was able to solve that but when I added the XL2BB Excel said macros were disabled. When I try to select items mini sheet capture range is shaded or not availible...