Starting point - picture of pipe network. Ending point - calculation of longest route!

So, what would your expected outcome be? What is the answer you are looking for based on the diagramme? What is the manual calculation?

I'm not clear in my own mind how this would work, and you have not indicated how it might change or how Excel would get the data, but I suppose that this might be a starting point:

Excel 2016 (Windows) 32 bit

	B	C	D	E
2	Phase	Start	End	Length
3	1	Y	N	8
4	2	N	Y	5
5	2	N	N	10
6	3	N	Y	1
7	3	N	N	4
8	4	N	Y	5

Sheet: Sheet1

We probably need to know more about the variables before trying to offer a formula or VBA solution.

To solve for the general case, you'd start by labeling the nodes and the distances between:

	A	B	C	D	E	F	G
A		8
B	8		5	10
C		5
D		10			1	4
E				1
F				4			5
G						5

Then write some code that does a graph traversal (like running a maze), evaluate all possible paths that don't visit the same node twice, and select the longest. The code to do this is fussy, and the run time goes up exponentially with the numbers of nodes and connections.

Here's an example:

	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	From\To	A	B	C	D	E	F	G	H	I	J		Node	Distance
4	A			88						57	88		J			O4:O13: {=TRANSPOSE(LongestPath(C3:M13))}
5	B				57			58					A	92		P5: =INDEX($D$4:$M$13, MATCH(O4, $D$3:$M$3, 0), MATCH(O5, $C$4:$C$13, 0))
6	C				27	39	54	69	47				I	57
7	D		88			59							F	53
8	E		32							14	25		H	82
9	F								82				C	99
10	G		19			79	91				39		D	27
11	H	49	35	99		20	92	94		65			B	88
12	I						53	27					G	58
13	J	92	23				85			16			E	79
14													Total	635		P14: =SUM(P3:relN)

Those 33 connections among 10 nodes required evaluation of 1463 paths.

I'll post the workbook if you're comfortable with a VBA solution.