Pilot trying to calcualte Night flight time

Colume B = Date in mm/dd/yyyy format
Column G = Depart Time In HH:MM (24Hour) Format
Column I = Flight time in number format ( ie 2.15 Hours)

What I would like is an If/else statement so that:

Regardless of year....

if Depart time is "March or before" or "November or after" AND after 18:00 (6pm) ....input value From Column I
Or
if Depart time is 20:00 (8pm)...........input Value from Column I

Else input value of "0"

So, what happens with a 2 hour flight that starts at 5:00pm in the Winter months (or at 7:00pm in the Summer months) ? Should these be counted as 1 hour night flight time ?

Pete

I figured that i didn't need to get THAT accurate being that I'm just using months instead winter and summer soltice of actual night and day times

SO far all i have is this:

=IF(OR(AND($B2<DATE(2013,3,1),$G2>TIME(16,0,0)), AND($B2>DATE(2013,11,1),$G2>TIME(16,0,0)),$G2>TIME(20,0,0)),$I2,"0")

Okay, try this:

=IF(AND(MONTH(B2)>3,MONTH(B2)<11)),IF(G2>=--"20:00:00",I2,0),IF(G2>=--"18:00:00",I2,0))

Hope this helps.

Pete

Originally Posted by Pete_UK

Okay, try this:

=IF(AND(MONTH(B2)>3,MONTH(B2)<11)),IF(G2>=--"20:00:00",I2,0),IF(G2>=--"18:00:00",I2,0))

Hope this helps.

Pete

Thanks Pete, i figured it out using your help.

=IF(OR(AND($B12<=MONTH(3),$G12>=TIME(17,0,0)), AND($B12>=MONTH(11),$G12>=TIME(17,0,0)),$G12>=TIME(20,0,0)),$I12,"0")

The MONTH function was great!! I also used TIME(hour,min,sec)

It worked for April 2013........I'm gonna try importing some of the other months before April and see if it still works.

MONTH needs to act on a date, or a cell containing a date. MONTH(3) and MONTH(11) will always return 1, as those numbers will be interpreted as being within January 1900. Also, if you return "0" this will be a text value - you do not need the quotes around the 0. You can change my formula to include the TIME function like this:

=IF(AND(MONTH(B2)>3,MONTH(B2)<11)),IF(G2>=TIME(20,0,0),I2,0),IF(G2>=TIME(17,0,0),I2,0))

You seem to have changed the time for the winter months to 5:00pm, so I have also done this in the above formula.

Hope this helps.

Pete