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

Originally Posted by Pete_UK

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

I see what your equation is doing...its checking for summer (which i kinda like better)

The only problem is that the IF function can only do one check and return values for two conditions..True or False

Therefore an OR Function needed to be added so that multiple conditions could be entered

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

I also like your idea of changing the "0" to a 0 (zero)...............I think i might actually take it one step further and have it return a blank "". Unless you know of a way to make zeros come up as blanks and not appear.

Dave

It's a question of how you apply the logic. In my formula, as you deduce, I'm saying IF it's summer then the time condition is 8:00pm but IF NOT (i.e. Winter) then the time condition is 5:00pm, but it's easy enough to turn this round if you want to. It's just that the Summer condition is slightly easier to formulate.

If you return 0, you can get it to appear blank by applying conditional formatting - if the cell value is zero then use white foreground colour, so that it appears to be empty.

Hope this helps.

Pete

Hi Pete

Hope your well.I hope you can help me.

I saw your post in 2013 on the pilot log calculation for night flying time.

I'm trying to do the same thing but for International flights say taking off at Johannesburg at 18:00 UTC time and landing in London on the next day at 05:00 UTC.
I got a formula from another website that uses spherical trigonometry,wich is above my skill level.

Do you know anything about that.Here's the URL if you are able to help and got the time.

I will really appreciate it.

Frikkie Grabie
[email protected]

http://physics.stackexchange.com/que...uring-a-flight