Sum hours per day between dates

[nemmi69]

I have seen the formula to sum between dates but I need to generate a total of hours (End_Time-Start_Time) for each of the days between two dates. IE 24/11/2022 to 25/11/2022 from a table like the one below

 

[Alex Blakenburg]

Do you want an additional column with the difference ?
Do any of the End_Times run into the next day ie past midnight ?
What is the name of your table ?

 

[Alex Blakenburg]

See if this is what you had in mind.

20221130 Hours calculation nemmi69.xlsx
	A	B	C	D
1
2	Date	Start_Time	End_Time	Hours
3	24-Nov	8:00	9:00	1
4	24-Nov	9:00	13:00	4
5	24-Nov	13:00	15:00	2
6	24-Nov	15:00	18:00	3
7	24-Nov	18:00	18:45	0.75
8	24-Nov	18:45	19:15	0.5
9	24-Nov	23:45	0:15	0.5
Sheet1

Cell Formulas
Range	Formula
D3:D9	D3	=ROUND( IF(MOD([@[End_Time]],1)<MOD([@[Start_Time]],1), (1+MOD([@[End_Time]],1)-[@[Start_Time]])*24, ([@[End_Time]]-[@[Start_Time]])*24), 2)

 

[nemmi69]

Thank you but the From and To dates will change and it will only be the individual days between those dates IE 24/11/2022 to 25/11/2022
Date Hours
24-Nov 11:15
25-Nov 10:00

 

[Dave Patton]

A variety of solutions for you. It would be much better if you posted an extract of your data and expected results with the forum's tool named XL2BB!

Time.xlsm
	A	B	C	D	E	F	G	H
24	Date	Start	End
25	24 Nov	08:00	09:00			24 Nov	11:15	11:15
26	24 Nov	09:00	13:00			25 Nov	14:40	14:40
27	24 Nov	13:00	15:00
28	24 Nov	15:00	18:00
29	24 Nov	18:00	18:45
30	24 Nov	18:45	19:15
31	25 Nov	08:00	09:00
32	25 Nov	10:15	23:55
5b

Cell Formulas
Range	Formula
G25:G26	G25	=SUMPRODUCT(--($A$25:$A$32=F25),$C$25:$C$32-$B$25:$B$32)
H25:H26	H25	=SUM(IF($A$25:$A$32=F25,$C$25:$C$32-$B$25:$B$32))

Time.xlsm
	A	B	C	D	E	F	G	H
1	Date	Start	End					Time sum
2	24 Nov	08:00	09:00	01:00		24 Nov	11:15
3	24 Nov	09:00	13:00	04:00		25 Nov	14:40
4	24 Nov	13:00	15:00	02:00
5	24 Nov	15:00	18:00	03:00
6	24 Nov	18:00	18:45	00:45
7	24 Nov	18:45	19:15	00:30
8	25 Nov	08:00	09:00	01:00
9	25 Nov	10:15	23:55	13:40
10
11	Date	Start	End			Excel enters the formulas use Data Subtotal
18	24 Nov Total			11:15
21	25 Nov Total			14:40
22	Grand Total			25:55
5b

Cell Formulas
Range	Formula
G2:G3	G2	=SUMIFS($D$2:$D$9,$A$2:$A$9,F2)
D2:D9	D2	=C2-B2
D18	D18	=SUBTOTAL(9,D12:D17)
D21	D21	=SUBTOTAL(9,D19:D20)
D22	D22	=SUBTOTAL(9,D12:D20)

 

[nemmi69]

Looks good Dave, but the dates will be selected by user so need to be able to dynamically change.

 

[nemmi69]

I can manage it if someone can figure how to get from the From to dates in B1 & B2 to the dates in E2 to E5 so that if a wider range is entered it will expand to cover

 

[nemmi69]

Sorted!!

Table name "TblDiary" in Sheet "DiaryPage"

Sheet name "Results"

AB4 =DATEVALUE($B$3)
AB5, AB6 . . . =IF($B$3+ROW(A1)<=DATEVALUE($B$4),AB4+1,"")

AC4, AC6 . . . =IF(AB4<>"",IF(SUMIFS(TblDiary[Start_Time],TblDiary[Date],"="&Results!$AB4)<=0,"",SUMIFS(TblDiary[Start_Time],TblDiary[Date],"="&Results!$AB4)),"")
AD4, AD6 . . . =IF($AB4<>"",IF(SUMIFS(TblDiary[End_Time],TblDiary[Date],"="&Results!$AB4)<=0,"",SUMIFS(TblDiary[End_Time],TblDiary[Date],"="&Results!$AB4)),"")
AE4, AE6 . . . =IF($AB4="","",AD4-AC4)
AF4, AF6 . . . =IF($AB4="","",IF(AE4<$AG$2,(AE4-$AG$2)*-1,AE4-$AG$2))

 

[Alex Blakenburg]

Thanks for letting us know. Glad to hear you found a solution.