Hi I am trying to develop a query to determine how much time a pot is set underwater across the night (Column E) and day (Column F) based on the sunrise and sunset times of the location.

I have 7 columns:

Set (start) Time (Column A), Haul (end) Time (Column B), Sunrise (Column C), Sunset (Column D), Time of Day Fishing (Column E), Time of Night Fishing (Column F) Start Date (Column G)

Set (start)
Haul(end)
Sunrise Sunset

Day Time
Night Time

6:43:12 PM 7:35:35 AM 8:05:00 AM 5:29:00 PM ############# 14:06
12:14:24 PM 6:20:35 AM 8:04:00 AM 5:30:00 PM 5:15 12:50 6:57:36 AM 1:00:35 AM 8:03:00 AM 5:31:00 PM ############# ############# 1:55:12 PM 7:26:15 AM 8:03:00 AM 5:32:00 PM 3:36 13:54 2:52:48 PM 8:37:25 AM 8:02:00 AM 5:33:00 PM 2:40 15:04 7:26:24 PM
4:51:58 PM
8:10:00 AM 5:23:00 PM ############# 23:28

For example if someone sets a pot at 1:00pm and hauls it at 7.00pm and the sunset time is 6.00pm then that is 5:00 hours time of day fishing and 1:00 hour time of night fishing.

I have developed two queries using time of PM and AM one for Time of Day Fishing Column:
=IF(AND(A2>0, A2<0.499306),C2-A2,IF(AND(A2>=0.5, A2<0.999306), D2-A2))

and one for Time of Night Fishing Column:
=IF(AND(A2>0, A2<0.499306),C2-A2,IF(AND(A2>0.5, A2<0.999306), B2-D2))

However I find that while I can get some correct answers there are many that are ####### due to impossibility of catering for all times. If I change the date system to 1904 this doesn't necessarily correct the errors either. I therefore assume I need to work out a query that functions using the 1900 date system seeing the times are already in that mode.

Some examples of the errors include if the time set is 8am say and the haul time is 3pm (so before sunset) all the time (7 hours) should be in Column E and zero in Column F but I can't work out how to get the query to complete this. Another problem is if the set time goes over multiple days. For example if a pot is set at 7pm and then hauled at 8pm on the next day and sunrise was at 6am and sunset 6pm then the query can't calculate two periods of night fishing, one between 7pm and 6am and the second between 6pm-8pm on the second day.

Any assistance would be greatly appreciated.

Cheers
Tim