Sum of hours between a range of dates

[jennyd88]

Don't know if this is possible but thought someone out there may have some ideas....
Making a holiday work rota and trying to get the sum of the hours taken between two dates
This is basically how it looks:
Sheet 1
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Rota hours[/TD]
[/TR]
[TR]
[TD]10/11/2017[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]11/11/2017[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]12/11/2017[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]13/11/2017[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]14/11/2017[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2[TABLE="class: outer_border, width: 300"]
<tbody>[TR]
[TD]Holiday start date[/TD]
[TD]Holiday end date[/TD]
[TD]Total hours holiday[/TD]
[/TR]
[TR]
[TD]10/11/2017[/TD]
[TD]13/11/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Total hours holiday would be the sum of column B between those 2 dates on sheet 1.

Anyone any ideas

 

[jennyd88]

Sorted, just used a SUMIFS formula

 

[jim may]

Excel 2010

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]Holiday start date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Holiday end date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Total hours holiday[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]10/11/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]13/11/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]31[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=SUMPRODUCT((Sheet1!$A$2:$A$6>=Sheet2!A2)*(Sheet1!$A$2:$A$6<=Sheet2!B2)*Sheet1!$B$2:$B$6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]