Calculate the difference in hours in a range

[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, without having to write code in Visual Basic?

[Bernie Deitrick]

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IFERROR(TIMEVALUE(MID(G8,1+(ROW($A$1:$A$12)-1)*12+6,5)),0)-IFERROR(TIMEVALUE(MID(G8,1+(ROW($A$1:$A$12)-1)*12,5)),0))

If you want hours (for your multiplication) use:

=SUM(IFERROR(TIMEVALUE(MID(G8,1+(ROW($A$1:$A$12)-1)*12+6,5)),0)-IFERROR(TIMEVALUE(MID(G8,1+(ROW($A$1:$A$12)-1)*12,5)),0))*24

also array-entered.

If you could have more than 12 rows of time differences in one cell, change the $A$12 to $A$-something larger.

[marduk87RP]

I try this formula but tells me the formula contains an error

[Tsjallie]

This should work and be flexible.

{=SUM(IFERROR(TIMEVALUE(MID(CLEAN($G8),(ROW(INDIRECT("1:"&LEN(G8)-LEN(SUBSTITUTE(G8,"-",""))))-1)*11+7,5)),0)-IFERROR(TIMEVALUE(MID(CLEAN($G8),(ROW(INDIRECT("1:"&LEN(G8)-LEN(SUBSTITUTE(G8,"-",""))))-1)*11+1,5)),0))}

Note that it is an array formula. Close with [Ctrl]+[Shift]+[Enter]

Explaination:
It's about this part of the formula:

....MID(Text,StartPos,NumOfChars).....(ROW(INDIRECT("1:"&LEN(G8)-LEN(SUBSTITUTE(G8,"-",""))))-1)*11+7....

Text is G8 where Clean() strips off any unprintable characters like CR and/or LF.

StartPos is calculated by

....MID(Text,StartPos,NumOfChars).....(ROW(INDIRECT("1:"&LEN(G8)-LEN(SUBSTITUTE(G8,"-",""))))-1)*11+7....

for the right time and

....MID(Text,StartPos,NumOfChars).....(ROW(INDIRECT("1:"&LEN(G8)-LEN(SUBSTITUTE(G8,"-",""))))-1)*11+1....

for the left time
The StartPos is different for each line and needs to be calculated by x*11+7 and x*11+1. Where x = {0,1,2,3,4,5,6}
The function ROW(1:7)-1 gives you the array x. However you need to construct that function with INDIRECT() as ROW() doesn't directly take variable values.
The number of elements in the array x is determined by the number of lines in the cell. That is calculated by counting the number of "-" in the cell.

Once this way times are isolated they are converted to their time values and these are subtracted to get the elapsed times.

IFERROR is there because an empty cell in column G would give an error.

[Bernie Deitrick]

Here's a working file with my formula.... The error is likely due to region and your separator not being a comma..... Opening the file should work for you. If not, try these array formulas:

=SUM(IFERROR(TIMEVALUE(MID(G8;1+(ROW($A$1:$A$12)-1)*12+6;5));0)-IFERROR(TIMEVALUE(MID(G8;1+(ROW($A$1:$A$12)-1)*12;5));0))

If you want hours (for your multiplication) use:

=SUM(IFERROR(TIMEVALUE(MID(G8;1+(ROW($A$1:$A$12)-1)*12+6;5));0)-IFERROR(TIMEVALUE(MID(G8;1+(ROW($A$1:$A$12)-1)*12;5));0))*24