Nested Logical Operators Within Data Ranges

[topherhaddad]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Day Duration Start[/TD]
[TD]Day Duration End[/TD]
[TD]Day[/TD]
[TD]Total Duration Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.2[/TD]
[TD]1.5[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1.6[/TD]
[TD]1.8[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2.3[/TD]
[TD]3.4[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5.3[/TD]
[TD]5.8[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5.8[/TD]
[TD]6.1[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need to calculate the total duration time per day for a large amount of column data in multiple worksheets. The tricky part is, some durations span across multiple days, happen multiple times in a day, or don't happen at all in a day. I need some sort of formula that takes all this into account, and I'm guessing it will be some sort of nested if functions that test all data in the row and then sum the relevant duration intervals. Can anyone help? Thank you!

 

[etaf]

i dont understand the data shown - can you explain - i dont see any dates nor times

 

[topherhaddad]

I apologize, the data isn't very clear. The real data is in total seconds so it is more difficult to understand, so I tried to simplify it here.

e.g. the total duration time for day 1 = (1.5-1.2) + (1.8-1.6) = .5 of a day
So basically I want the result to be from differences in column B and C but to include the relevant sums of duration times spanning multiple days or multiple intervals in a day.

Does that make more sense?

 

[topherhaddad]

I apologize, the data isn't very clear. The real data is in total seconds so it is more difficult to understand, so I tried to simplify it here.

e.g. the total duration time for day 1 = (1.5-1.2) + (1.8-1.6) = .5 of a day
So basically I want the result to be from differences in column B and C but to include the relevant sums of duration times spanning multiple days or multiple intervals in a day.

Does that make more sense?

 

[etaf]

why is day 1 (1.8-1.6) ??
it says day 2 in column C

 

[topherhaddad]

[TABLE="width: 508"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Day Start Duration[/TD]
[TD="align: left"]Day End Duration[/TD]
[TD="align: left"]Day [/TD]
[TD="align: left"]Duration Per Day[/TD]
[/TR]
[TR]
[TD="align: right"]1.4886[/TD]
[TD="align: right"]1.4909[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.0333[/TD]
[TD="align: right"]2.0350[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.4756[/TD]
[TD="align: right"]2.4779[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.0200[/TD]
[TD="align: right"]3.0224[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.4630[/TD]
[TD="align: right"]3.4646[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4.0071[/TD]
[TD="align: right"]4.0093[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4.9947[/TD]
[TD="align: right"]4.9957[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5.5023[/TD]
[TD="align: right"]5.5036[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6.4888[/TD]
[TD="align: right"]6.4911[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7.0334[/TD]
[TD="align: right"]7.0352[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7.4758[/TD]
[TD="align: right"]7.4781[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8.0201[/TD]
[TD="align: right"]8.0225[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8.4632[/TD]
[TD="align: right"]8.4648[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9.0072[/TD]
[TD="align: right"]9.0095[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9.9948[/TD]
[TD="align: right"]9.9958[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10.5024[/TD]
[TD="align: right"]10.5038[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11.4889[/TD]
[TD="align: right"]11.4912[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12.0336[/TD]
[TD="align: right"]12.0353[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12.4759[/TD]
[TD="align: right"]12.4783[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13.0203[/TD]
[TD="align: right"]13.0227[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13.4633[/TD]
[TD="align: right"]13.4649[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14.0074[/TD]
[TD="align: right"]14.0096[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14.9949[/TD]
[TD="align: right"]14.9960[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15.5026[/TD]
[TD="align: right"]15.5039[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16.4891[/TD]
[TD="align: right"]16.4914[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17.0337[/TD]
[TD="align: right"]17.0355[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17.4761[/TD]
[TD="align: right"]17.4784[/TD]
[TD="align: right"]27[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18.0204[/TD]
[TD="align: right"]18.0228[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18.4635[/TD]
[TD="align: right"]18.4651[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19.0075[/TD]
[TD="align: right"]19.0098[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19.9951[/TD]
[TD="align: right"]19.9962[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20.5028[/TD]
[TD="align: right"]20.5040[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21.4892[/TD]
[TD="align: right"]21.4915[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22.0339[/TD]
[TD="align: right"]22.0356[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22.4762[/TD]
[TD="align: right"]22.4786[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23.0206[/TD]
[TD="align: right"]23.0230[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23.4636[/TD]
[TD="align: right"]23.4652[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24.0077[/TD]
[TD="align: right"]24.0099[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24.9952[/TD]
[TD="align: right"]24.9964[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25.5029[/TD]
[TD="align: right"]25.5042[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26.4894[/TD]
[TD="align: right"]26.4917[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27.0341[/TD]
[TD="align: right"]27.0357[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27.4764[/TD]
[TD="align: right"]27.4787[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28.0208[/TD]
[TD="align: right"]28.0231[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28.4638[/TD]
[TD="align: right"]28.4654[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29.0078[/TD]
[TD="align: right"]29.0101[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29.9953[/TD]
[TD="align: right"]29.9965[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30.5031[/TD]
[TD="align: right"]30.5043[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[topherhaddad]

Here is the actual data from one of the sheets. It is confusing in my first example, but the numbered day rows do not actually correspond to its respective row data. The time durations that occur happen multiple times each day, and so I need to calculated the total duration for each day. I also need to account for days that no time duration happens, which is on some of my other sheets.

e.g.
for row Day 1 - add up all the time duration in range H2:I50 that correspond to day 1
for row Day 2 - add up all the time duration in range H2:I50 that correspond to day 2

H2:I50 is the two columns that go with day start duration and day end duration

 

[topherhaddad]

I really appreciate your help, Wayne. Sorry for being confusing.

 

[etaf]

sorry i'm not getting it
i dont know how to identify the days
if you put the duration into cell K2
as
I2-H2

and then use a SUMIF() for each day - if i knew what a day was

 

[thisoldman]

Do all dates that are same day have the same integer? In other words, all day ones are numbers that begin with one point something and all day twos begin with two point something?

Is this true: 1.11, 1.23, and 1.37 are all day one?
Is this true: 1.11, 2.23, and 3.37 are consecutive days—day one, day two and day three?