Help with complicated IF Query

[limpbizkit]

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)

<table width="758" border="0" cellpadding="0" cellspacing="0"><col width="92" span="2"><col width="74"><col width="97"><col width="97"><col width="100"><col width="103" span="2"><tr height="20"> <td class="xl66" style="height: 15pt; width: 69pt;" width="92" height="20">Set (start)
</td> <td class="xl69" style="border-left: medium none; width: 69pt;" width="92"> Haul(end)</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="74">
</td> <td class="xl69" style="border-left: medium none; width: 73pt;" width="97">Sunrise</td> <td class="xl70" style="border-left: medium none; width: 73pt;" width="97">Sunset
</td> <td class="xl69" style="border-left: medium none; width: 75pt;" width="100">
</td> <td class="xl70" style="border-left: medium none; width: 77pt;" width="103">Day Time
</td> <td class="xl70" style="border-left: medium none; width: 77pt;" width="103">Night Time
</td> </tr></table>
<table width="1199" border="0" cellpadding="0" cellspacing="0"><col style="width: 77pt;" width="102"> <col style="width: 76pt;" width="101"> <col style="width: 102pt;" width="136"> <col style="width: 98pt;" width="130"> <col style="width: 76pt;" width="101"> <col style="width: 58pt;" width="77"> <col style="width: 69pt;" width="92" span="2"> <col style="width: 56pt;" width="74"> <col style="width: 73pt;" width="97"> <col style="width: 73pt;" width="97"> <col style="width: 75pt;" width="100"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 77pt;" width="102" height="20">6:43:12 PM</td> <td class="xl69" style="border-left: medium none; width: 76pt;" width="101">7:35:35 AM</td> <td class="xl66" style="width: 56pt;" width="74" align="right">8:05:00 AM</td> <td class="xl66" style="width: 73pt;" width="97" align="right">5:29:00 PM</td> <td class="xl67" style="width: 73pt;" width="97" align="center">#############</td> <td class="xl67" style="width: 75pt;" width="100" align="right">14:06
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none; width: 77pt;" width="102" height="20">12:14:24 PM</td> <td class="xl69" style="border-top: medium none; border-left: medium none; width: 76pt;" width="101">6:20:35 AM</td> <td class="xl66" align="right">8:04:00 AM</td> <td class="xl66" align="right">5:30:00 PM</td> <td class="xl67" align="right">5:15</td> <td class="xl67" align="right">12:50</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none; width: 77pt;" width="102" height="20">6:57:36 AM</td> <td class="xl69" style="border-top: medium none; border-left: medium none; width: 76pt;" width="101">1:00:35 AM</td> <td class="xl66" align="right">8:03:00 AM</td> <td class="xl66" align="right">5:31:00 PM</td> <td class="xl67" align="center">#############</td> <td class="xl67" align="center">#############</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none; width: 77pt;" width="102" height="20">1:55:12 PM</td> <td class="xl69" style="border-top: medium none; border-left: medium none; width: 76pt;" width="101">7:26:15 AM</td> <td class="xl66" align="right">8:03:00 AM</td> <td class="xl66" align="right">5:32:00 PM</td> <td class="xl67" align="right">3:36</td> <td class="xl67" align="right">13:54</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none; width: 77pt;" width="102" height="20">2:52:48 PM</td> <td class="xl69" style="border-top: medium none; border-left: medium none; width: 76pt;" width="101">8:37:25 AM</td> <td class="xl66" align="right">8:02:00 AM</td> <td class="xl66" align="right">5:33:00 PM</td> <td class="xl67" align="right">2:40</td> <td class="xl67" align="right">15:04</td> </tr> </tbody></table><table width="996" border="0" cellpadding="0" cellspacing="0"><col width="136"><col width="130"><col width="101"><col width="77"><col width="92" span="2"><col width="74"><col width="97"><col width="97"><col width="100"><tr height="20"> <td class="xl69" style="height: 15pt; width: 102pt;" width="136" height="20">7:26:24 PM
</td> <td class="xl69" style="border-left: medium none; width: 98pt;" width="130">4:51:58 PM
</td> <td class="xl67" style="width: 56pt;" width="74" align="right">8:10:00 AM</td> <td class="xl67" style="width: 73pt;" width="97" align="right">5:23:00 PM</td> <td class="xl68" style="width: 73pt;" width="97" align="center"> #############</td> <td class="xl68" style="width: 75pt;" width="100" align="right">23:28</td> </tr></table>

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

 

[GlennUK]

Another problem is if the set time goes over multiple days.

Just a quick question ... will the multiple days only ever be one day to the next, or may it be one day to some other day in the future ( 2 or 3 or more days )?

 

[limpbizkit]

Hey Glenn,

It is unusual but yes some go over more than one day.

Cheers
Tim

 

[GlennUK]

OK, I'll have a think about this during my lunchtime today. Of course, someone else might quickly spot a great way to do this in the meantime ( fingers crossed ).

 

[energman58]

Hi

Are your times excel times (ie. if you change the format to a number do you get a long number with decimals) or are they some weird format text from a datalogger or something?

I cant see how without including the date (ie excel format times) you can tell if the thing has been out there 10 minutes or 24 hours and 10 minutes and if it is longer than 24 hours your data format wont let you develop a simple formula because at least one of the sunrises/sunsets you are interested in wont be available in that row.

Also is there data for every day or will there be gaps?

 

[GlennUK]

I also think that you'd need dates in there. And maybe a separate table for the sunrise and sunset times per day.