Help with IF Statement

Colin Legg · #4 02-24-2011, 04:45 AM

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:

Code:

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Invoice Statement	Shoshana3	Word	0	11-15-2010 03:02 PM
if statement	piper7971	PowerPoint	1	08-19-2010 07:10 AM
Help with IF statement!	CPelkey	Word	1	04-12-2010 09:06 AM
Have you seen this error statement?	nebb	Word	4	12-01-2009 10:05 AM
Vlookup and If statement problem	bunnygum	Excel	1	03-24-2009 05:10 AM