sumif between date and time

[kouangooo]

Hi there,
I have following data:
A= Date + Time
B = Date
C= Time
D= Amount
I used Text to column to split the date / time (if that can help ?)

What I would need is to calc the sum of all transaction between certain time frame (between 7h and 11h for example) and per day

So the result should look like +/- something like this :

	Breakfast (7h-10h)	Lunch (11h - 15h)
1/1/18
2/1/18

<tbody>
</tbody>

I've been trying to figure out the right formulas for some time but can't come up with anything correct...

Thanks for your help

12-01-2018 07:32:12	12-01-2018	07:32:12	10
26-01-2018 12:26:55	26-01-2018	12:26:55	20
08-01-2018 18:34:15	08-01-2018	18:34:15	30

<tbody>
</tbody><colgroup><col><col><col span="2"></colgroup>

 

[Dave Patton]

Please consider the following and clarify your question.

Excel 2010
	A	B	C	D	E	F
1	Start	End	Total Time	7:00	11:00	15:00
2	12-Jan-18 7:32	12-Jan-18 22:32	15.00	3.46	4.00	7.54
3	26-Jan-18 12:26	26-Jan-18 22:30	10.05	0.00	2.55	7.50
4	08-Jan-18 18:34	08-Jan-18 22:30	3.93	0.00	0.00	3.93
5			28.98	3.46	6.55	18.97
6
8c

Cell Formulas
Range	Formula
C2	=ROUND((B2-A2)*24,2)
C5	=SUM(C2:C4)
D2	=(MOD(A2,1)<$E$1)*((MIN(MOD(B2,1),$E$1)-MAX(MOD(A2,1),$D$1))*24)
E2	=(MOD(A2,1)<$F$1)*((MIN(MOD(B2,1),$F$1)-MAX(MOD(A2,1),$E$1))*24)
F2	=C2-SUM(D2:E2)

Edit and expand this example to address your challenge.
7:00 column means start at 7: to 11:00.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1	Start		End	Total Time	7:00	11:00	15:00
2	12-Jan-18 7:30		12-Jan-18 22:30	15.00	3.50	4.00	7.50
3	26-Jan-18 12:26		26-Jan-18 22:30	10.05	0.00	2.55	7.50
4	08-Jan-18 18:34		08-Jan-18 22:30	3.93	0.00	0.00	3.93
5				28.98	3.50	6.55	18.93
6
7
8	Start		End
9	12-Jan-18 7:30	07:30:00	22:30	15.00	3.50	4.00	7.50
10	26-Jan-18 12:26	12:26:55	22:30	10.05	0.00	2.55	7.50
11	08-Jan-18 18:34	18:34:15	22:30	3.93	0.00	0.00	3.93
12				28.98	3.50	6.55	18.93
13
8c

Cell Formulas
Range	Formula
D2	=ROUND((C2-A2)*24,2)
D9	=ROUND((C9-B9)*24,2)
E2	=(MOD(A2,1)<$F$1)*((MIN(MOD(C2,1),$F$1)-MAX(MOD(A2,1),$E$1))*24)
E9	=(B9<$F$1)*((MIN(C9,$F$1)-MAX(B9,$E$1))*24)
F2	=(MOD(A2,1)<$G$1)*((MIN(MOD(C2,1),$G$1)-MAX(MOD(A2,1),$F$1))*24)
F9	=(B9<$G$1)*(MIN(C9,$G$1)-MAX(B9,$F$1))*24
G2	=D2-SUM(E2:F2)
G9	=D9-SUM(E9:F9)
B9	=MOD(A9,1)
B10	=MOD(A10,1)
B11	=MOD(A11,1)