Hi,
I've made a few adjustments.
In the look up table, I've added the dates in the left hand column and day hours in the right hand column.
In your fishing data table, I've added columns for sunrise and sunset on both the Start date and the End date.
Then, to calculate the day hours we use a formula made of three components:
1. Calculate the day hours on the start date:
Code:
IF(AND(C2>D2,B2<E2),MIN(C2,E2)-MAX(B2,D2),0)
2. Calculate the day hours on the end date:
Code:
IF(INT(C2)>INT(B2),IF(C2>F2,MIN(C2,G2)-MAX(B2,F2),0),0)
3. Calculate the day hours on any dates between the start date and the end date:
Code:
SUMIFS('Sunrise Sunset'!D:D,'Sunrise Sunset'!A:A,">"&INT(B2),'Sunrise Sunset'!A:A,"<"&INT(C2))
Put it all together:
Code:
=IF(AND(C2>D2,B2<E2),MIN(C2,E2)-MAX(B2,D2),0)
+IF(INT(C2)>INT(B2),IF(C2>F2,MIN(C2,G2)-MAX(B2,F2),0),0)
+SUMIFS('Sunrise Sunset'!D:D,'Sunrise Sunset'!A:A,">"&INT(B2),'Sunrise Sunset'!A:A,"<"&INT(C2))
Then to get the night hours, we just take the total hours and subtract the day hours:
A couple of rows, eg row 16, return the wrong number because your end date/time is earlier than your start date/time.
*I couldn't test the formula because I only have XL 2003 at work which cannot use SUMIFS().