Automated Status Cell

[NrthnChrs]

Hello,

Im wondering if you could help with 2 formulas on a Action Tracker im building which will be used by a team to continually monitor and manage their work. Ideally we want to capture items which don't look achievable so we can reorganize resources and items which are overdue

Formula 1 - I have a cell which is Achievable, it uses Days To Go and Duration (Days), then if there are enough days to complete given Date Today and Duration it displays YES and if not it displays NO. I want to know if its possible to tie % complete into this. So if the item is 50% complete, this is factored into the formula and the Duration (Days) would in this case be reduced by 50%,

Formula 2 - I want to automate the status tab, I want to show Complete, Overdue, Needs Assistance, In Progress. Rank should be Complete, Overdue, Needs Assistance, In Progress.
Complete would be when %Complete = 100% or there is a Completed Date
Overdue would be when Date Today is > Due Date
Needs Assistance would be when Achievable is NO
In Progress would be when it is < due date and Achievable is YES

*Will post Mini Sheet separately

Thanks in advance for any help you can provide :D

 

[NrthnChrs]

Test 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Saturday, 25 June 2022
2								Working Days to Go:		(1-10 Red, 11-20 Amber, Overdue Black)
3			Task Log
4
5		Ref	Description of Task	Group	Owner	Start Date	Duration (Days)	Started (Y/N)	Due Date	Days to go	Action Required	Action Taken	% Complete	Acheivable	Completed Date	Status
6		1	test	Committees	Name 2	6/25/22	10	Y	7/5/22	7	test	test	50%	NO
7		2
8		3
9		4
10		5
11		6
12		7
13		8
14		9
15		10
16		11
17		12
18		13
19		14
20		15
21		16
22		17
23		18
24		19
25		20
26		21
27		22
28		23
29		24
Task Log

Cell Formulas
Range	Formula
C1	C1	=NOW()
J6:J29	J6	=IF(F6=0,"",IFERROR((NETWORKDAYS(I6,$C$1))*-1,0))
N6:N29	N6	=IF(F6=0,"",IF(G6<=J6,"YES","NO"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N:N	Cell Value	contains "YES"	text	NO
N:N	Cell Value	contains "NO"	text	NO
P:P	Cell Value	contains "Overdue"	text	NO
P:P	Cell Value	contains "Completed"	text	NO
J6:J672	Cell Value	=0	text	NO
J6:J672	Cell Value	<0	text	NO
J6:J672	Cell Value	>0	text	NO
J6:J672	Cell Value	="""N/A"""	text	YES
J6:J672	Cell Value	between 11 and 20	text	NO
J6:J672	Cell Value	between 1 and 10	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H6:H29	List	=Lists!$C$2:$C$3
D6:D672	List	=Lists!$B$2:$B$11
E6:E672	List	=Lists!$A$2:$A$8
P6:P29	List	=Lists!$D$2:$D$4