Help with IF Statement

[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)

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

[Colin Legg]

Hi Tim,

It would be easier to follow if you attached a sample worksheet to the thread but I think the problem is that you are only putting times in the cells (rather than dates and times).

Instead of putting 5:23:00 PM in a cell, put 01/03/2011 5:23:00PM. If all the cells are set up this way then you can easily subtract them from each other and periods which span across multiple dates will automatically be accounted for.

To deal with the Sunrise / Sunset issue, I suggest you keep a separate table which lists the sunrise and sunset times by date. You can then use a VLOOKUP() to retrieve the sunrise and sunset for the relevant date - since you would now be including the dates in the cells (not just the times) you can look up the correct sunrise/sunset dates without a problem.

Once you have this set-up your calculations will become much easier because all of the underlying numbers will be correct. Feel free to attach an example to the thread and I'll help you put some formulas in place to calculate the day/night hours for each record.

[limpbizkit]

Thanks for the reply Colin. I have created a new spreadsheet with start (set) and end (haul) times and dates integrated. I have also got a separate sheet with sunrise and sunset dates and times integrated as requested. I tried my old VLOOKUP formula but it didn't seem to work now with these integrated? So I pasted in the old times anyway from the other spreadsheet.

Anyways any help again would be appreciated.

Cheers
Tim

[Colin Legg]

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:

IF(AND(C2>D2,B2<E2),MIN(C2,E2)-MAX(B2,D2),0)

2. Calculate the day hours on the end date:

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:

SUMIFS('Sunrise Sunset'!D:D,'Sunrise Sunset'!A:A,">"&INT(B2),'Sunrise Sunset'!A:A,"<"&INT(C2))

Put it all together:

=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:

=C2-B2-H2

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().

[limpbizkit]

That's worked it perfectly, thanks so much for your help Colin, I would never have worked it out that way on my own.

Cheers
Tim