Project status logging

[Maverick_NL]

Hi,
I'm trying to make an overview of all projects that have been started and in what status they are in. To make a management summary I want to log how long (days) the status "open" or the status "Hold" is activated for the project.
But when the X shift between the columns the last status is not retained. When the status changes back it starts counting again from start till today "=IF(D3="X";TODAY()-C3;NA())"
Is there a way to count the days that the project has a status even when it switches between open and hold?
Thanks in advance!

Example.xlsx
	B	C	D	E	F	G	H	I	J	K
1	Project Place	Status
2		Date Start	Open	Hold	Date completed	Date Stopped	Open	Hold	Completed	Stoppped
3	Productivity & Sustainability	10-1-2022	X		24-2-2022		246	#N/A	45	#N/A
4	Research & Scientific	17-1-2022	X			24-1-2022	239	#N/A	#N/A	239
5	Technology Platform	7-2-2022	X				218	#N/A	#N/A	#N/A
6	Technology Platform	14-2-2022	X				211	#N/A	#N/A	#N/A
7	New Product Development	28-2-2022	x				197	#N/A	#N/A	#N/A
8	New Product Development	4-3-2022	X	x			193	193	#N/A	#N/A
9	Research & Scientific	18-4-2023		X			#N/A	-217	#N/A	#N/A
10	Productivity & Sustainability	15-5-2022		X			#N/A	121	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
H3:H10	H3	=IF(D3="X",TODAY()-C3,NA())
I3:I10	I3	=IF(E3="X",TODAY()-C3,NA())
J3	J3	=IF(ISBLANK(F3),NA(),F3-C3)
K3:K10	K3	=IF(ISBLANK(G3),NA(),TODAY()-C3)
J4:J10	J4	=IF(ISBLANK(F4),NA(),TODAY()-C4)
C4,C6	C4	=C3+7
C5	C5	=C4+21
C7	C7	=C6+14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H1:XFD1,3:1048576,C2:XFD2,A1:C1	Cell	contains an error	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3:B10	List	=Labels!$A$1:$A$4
D3:E10	List	=Labels!$A$7

 

[JamesCanale]

I'd do something different - Keep the data separate, so all the records are there, and just rebuild the report with formulas.

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Project	Action	Date		Project	Start Date	Open	Hold	Date Completed	Date Stopped	Open days	Hold days	Completed days	Stopped days
2	A	Start	1/1/2022		A	1/1/2022	X				212	44
3	B	Start	2/1/2022		B	2/1/2022			7/15/2022		164	0	61
4	C	Start	4/2/2022		C	4/2/2022			9/13/2022		124	40	1
5	A	Hold	5/16/2022		D	8/16/2022		X		8/26/2022	5	24		19
6	A	Resume	6/2/2022
7	B	Finish	7/15/2022
8	C	Hold	8/1/2022
9	A	Hold	8/15/2022
10	D	Start	8/16/2022
11	D	Hold	8/18/2022
12	D	Resume	8/21/2022
13	D	Hold	8/22/2022
14	D	Resume	8/24/2022
15	D	Hold	8/26/2022
16	C	Resume	9/10/2022
17	A	Resume	9/11/2022
18	C	Finish	9/13/2022
Sheet27

Cell Formulas
Range	Formula
E2:E5	E2	=UNIQUE(A2:A18)
F2:F5	F2	=XLOOKUP(E2#&"Start",A2:A18&B2:B18,C2:C18,"",0)
G2:G5	G2	=IF(OR(INDEX(SORT(FILTER($A$2:$C$18,$A$2:$A$18=E2),3,-1),1,2)="Start",INDEX(SORT(FILTER($A$2:$C$18,$A$2:$A$18=E2),3,-1),1,2)="Resume"),"X","")
H2:H5	H2	=IF(INDEX(SORT(FILTER($A$2:$C$18,$A$2:$A$18=E2),3,-1),1,2)="Hold","X","")
I2:I5	I2	=IFERROR(FILTER($C$2:$C$18,($A$2:$A$18=E2)*($B$2:$B$18="Finish")),"")
J2:J5	J2	=IF(H2="X",IFERROR(INDEX(SORT(FILTER($C$2:$C$18,($A$2:$A$18=E2)*($B$2:$B$18="Hold")),1,-1),1,1),""),"")
K2:K5	K2	=LET(z,FILTER($B$2:$C$18,$A$2:$A$18=E2), a,SORT(z,2), b,INDEX(a,SEQUENCE(ROWS(a)),2), c,INDEX(SORT(z,2),1,2), d,INDEX(b,SEQUENCE(ROWS(b)-1,1,2,1),1)-INDEX(b,SEQUENCE(ROWS(b)-1,1,1,1),1), e,SUM(INDEX(d,SEQUENCE((ROWS(d)+1)/2,1,1,2))), FF,IF(G2="X",e+TODAY()-MAX(b),e), g,SUM(INDEX(d,SEQUENCE(ROWS(d)/2,1,2,2))), HH,IF(H2="X",g+TODAY()-MAX(b),g), FF)
L2:L5	L2	=LET(z,FILTER($B$2:$C$18,$A$2:$A$18=E2), a,SORT(z,2), b,INDEX(a,SEQUENCE(ROWS(a)),2), c,INDEX(SORT(z,2),1,2), d,INDEX(b,SEQUENCE(ROWS(b)-1,1,2,1),1)-INDEX(b,SEQUENCE(ROWS(b)-1,1,1,1),1), e,SUM(INDEX(d,SEQUENCE((ROWS(d)+1)/2,1,1,2))), FF,IF(G2="X",e+TODAY()-MAX(b),e), g,SUM(INDEX(d,SEQUENCE(ROWS(d)/2,1,2,2))), HH,IF(H2="X",g+TODAY()-MAX(b),g), IFERROR(HH,0))
M2:N5	M2	=IF(I2<>"",TODAY()-I2,"")
Dynamic array formulas.