Calculate the difference in hours in a range write in a multiplelines

[marduk87RP]

I have a situation like this:
In one cell, I split the time ranges over several lines, for example
07: 15-10: 27
10: 35-11: 00
11: 15-12: 27
13: 15-13: 27
14: 15-18: 27
19: 15-20: 27
21: 15-23: 39
I know it would be more logical to have an hourly range divided over several lines, unfortunately I can't do otherwise ... I tried this formula:

RIGHT(REPLACE(TRIM(MID(REPLACE($G8;CHAR(10);REPT(" ";LEN($G8)));(0)*LEN($G8)+1;LEN($G8)));".";":");5)-LEFT(REPLACE(TRIM(MID(REPLACE($G8;CHAR(10);REPT(" ";LEN($G8)));(0)*LEN($G8)+1;LEN($G8)));".";":");5)+(RIGHT(REPLACE(TRIM(MID(REPLACE($G8;CHAR(10);REPT(" ";LEN($G8)));(1)*LEN($G8)+1;LEN($G8)));".";":");5)-LEFT(REPLACE(TRIM(MID(REPLACE($G8;CHAR(10);REPT(" ";LEN($G8)));(1)*LEN($G8)+1;LEN($G8)));".";":");5))

But this formula only calculates the first row(To the eye the calculation is also wrong) and doesn't consider the others, is there a way, a formula to do this, calculate the time differences and then add the total

 

[jasonb75]

This works with the example when I copy and paste it to excel, whether or not it will work with anything else is another matter entirely.

=SUMPRODUCT(AGGREGATE(15;6;--MID(SUBSTITUTE(G8;" ";"");(ROW(INDIRECT("1:"&(LEN(G8)-LEN(SUBSTITUTE(G8;":";"")))))-1)*6+1;5);ROW(INDIRECT("1:"&(LEN(G8)-LEN(SUBSTITUTE(G8;":";"")))/2))*2)-AGGREGATE(15;6;--MID(SUBSTITUTE(G8;" ";"");(ROW(INDIRECT("1:"&(LEN(G8)-LEN(SUBSTITUTE(G8;":";"")))))-1)*6+1;5);ROW(INDIRECT("1:"&(LEN(G8)-LEN(SUBSTITUTE(G8;":";"")))/2))*2-1))

As you noted in your post, it would be better to have the times split into multiple rows and columns, as such this is a possible workaround more than a solution.