Earned Value Formula Issue

[Consult123]

For those of you who don't know what earned value is, below is a link and some highlighted sections showing EV and SV. My spreadsheet with requirements is also attached.

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. Formula in column H is not working properly and aligned with parameters in SV key table to the top right.

If we can get the formula in column H working properly I would be extremely happy.!!!

Here's a link to potentially help Earned Schedule Management (ESM) - MPUG

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)		SV Key	Descriptions
3	TASK-001	Put laundry in hamper	1/1/2024	1/1/2024	100%	1/1/2024	0	ON SCHEDULE		LATE	>completion date and EV <100%
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. Example: EV at 50% & 50 of 100 days spent
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. Example: EV at 50% & 49 of 100 days spent
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

 

[Fluff]

Duplicate to: Percent Complete Versus Start and End Date Formulas For Conditional Formatting

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.