Sum Duration with Criteria Between Times

[Sully_T]

Hi All,

I usually think with enough googling and time I can my head round most issues but this one has me a bit stumped.

I have a log of recording status On/Off (could have other states too) with the start and end time. E:G

I would like to Sum the total time 'ON' during the interval in column A and B -
With just one time period i.e 8:00-9:00 I can do it on the lines in the log by making an adjustments to the start and end time based on the criteria, I suppose I could have a column for each period with an upper and lower criteria for each column...but that seems a messy solution.

So for example in the data below C4 = 00:42 C5 = 00:30 as it is only on for 30min between 9:00-10:00

Sum Times.xlsx
	A	B	C	D	E	F	G	H
1		Interval	01:00
2					Status	Ac Start	Ac End	Duration
3	Start	End	Duration On		ON	08:15	08:57	00:42
4	08:00	09:00			OFF	08:57	09:30	00:33
5	09:00	10:00			ON	09:30	10:50	01:20
6	10:00	11:00			OFF	10:50	11:10	00:20
7	11:00	12:00			ON	11:10	12:00	00:50
8	12:00	13:00			OFF	12:00	13:00	01:00
9	13:00	14:00			ON	13:00	18:00	05:00
10	14:00	15:00
11	15:00	16:00
12	16:00	17:00
13	17:00	18:00
Sheet1 (3)

Any help or pointers in the right direction would be greatly appreciated

Many thanks

 

[gaz_chops]

Welcome to the forum,

If you can alter the table or create a new one, with just the "ON"times, then this appears to work

=MIN(C4,INDEX($H$3:$H$6,MATCH(C4,$G$3:$G$6,1)))-MAX(B4,INDEX($G$3:$G$6,MATCH(C4,$G$3:$G$6),1))

 

[gaz_chops]

This one appears to work using your table as it is

=MIN(C4,INDEX($H$3:$H$9,MATCH(C4,IF($F$3:$F$9="ON",$G$3:$G$9),1)))-MAX(B4,INDEX($G$3:$G$9,MATCH(C4,IF($F$3:$F$9="ON",$G$3:$G$9),1)))

Enter as an array using Ctrl, Shift & Enter

 

[Sully_T]

That is brilliant, Thank you.

 

[gaz_chops]

You're welcome, thanks for the feedback.

 

[jasonb75]

Here's another method for you to try. With the example provided both give the same results, but I think that there may be differences if the times in columns F and G overlap, e.g. if you were summarising data for multiple people.

Book2
	A	B	C	D	E	F	G	H
1		Interval	01:00:00
2					Status	Ac Start	Ac End	Duration
3	Start	End	Duration On		ON	08:15:00	08:57:00	00:42:00
4	08:00:00	09:00:00	00:42:00		OFF	08:57:00	09:30:00	00:33:00
5	09:00:00	10:00:00	00:30:00		ON	09:30:00	10:50:00	01:20:00
6	10:00:00	11:00:00	00:50:00		OFF	10:50:00	11:10:00	00:20:00
7	11:00:00	12:00:00	00:50:00		ON	11:10:00	12:00:00	00:50:00
8	12:00:00	13:00:00	00:00:00		OFF	12:00:00	13:00:00	01:00:00
9	13:00:00	14:00:00	01:00:00		ON	13:00:00	18:00:00	05:00:00
10	14:00:00	15:00:00	01:00:00
11	15:00:00	16:00:00	01:00:00
12	16:00:00	17:00:00	01:00:00
13	17:00:00	18:00:00	01:00:00
Sheet1

Cell Formulas
Range	Formula
F4:F9	F4	=G3
H3:H9	H3	=G3-F3
C4:C13	C4	=SUM(IF($E$3:$E$13="ON",IF($G$3:$G$13>A4,IF($F$3:$F$13<B4,IF($G$3:$G$13>B4,B4,$G$3:$G$13)-IF($F$3:$F$13<A4,A4,$F$3:$F$13)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sully_T]

Thank you, I will bear that in mind and have a play with that too