Help with IF Statement

limpbizkit · #1 02-23-2011, 06:03 PM

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 · #2 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().

limpbizkit · #3 02-24-2011, 09:16 PM

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

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