Trying to Calculate day and night hours based on sunset and sunrise

I am a pilot and would like to create a lookup feature so that I do not always have to specify if a flight is day or night. I can enter all of the sun rise and sunset times from another source, I just need to figure out how to compare that with the times of my flight to determine if it was day or night, or some of both. I always have multiple legs on the same day, so the formula will have to know what date the flight was on to find the correct date for the sun rise and sunset times. I have posted an example day from the first of this month.

This slight variation on Dibb's solution will give the sunrise result you need.

Formula:

Please Login or Register  to view this content.

=IF(OR(H2>$C$7,H2<$B$7),"Nighttime Departure","Daylight Departure")&" & "&IF(OR(I2>$C$7,I2<$B$7),"NighttimeArrival","Daytime Arrival")

Formula:

Please Login or Register  to view this content.

Here is an example of my logbook so that I can clarify what I am looking for a little more. I would like to be able to put in the Departure and arrival times and then it figure out the day time and night time from that.

Currently the way that I do that is assume everything is day time, and then if I have some night flying I will enter that manually and my spreadsheet will subtract it from the day time.

@dredwolf, It would be great to be able to include multiple locations and time zones, but right now I am just trying to figure out how to do it with one location and one time zone then I can try to expand it from there.

FDibbins thanks for the starting formula. I was able to make it work for me after some modifications.

The next thing that I would like to be able to do is use multiple locations in my formula. I was thinking that I will choose which ever one is closest to the change in daylight. Like if it is a morning flight or evening flight and we are still closest to the departure airport, then I would use those times. But if we are closer to the destination airport when the light changes then I would use that.

I was thinking some sort of Vlookup, but I am not exactly sure how to make that work for me in this instance.

Thanks.

I am new member of your forum. I am also an airline pilot and my question is in continuation of davidhale87 to FDibbins for his reply which contains formulas,
in J14 =IF(G14>=$C$7,0,IF(H14<=$C$7,H14-G14,$C$7-G14))
in k14 =IF(G14>=$C$7,H14-G14,IF(H14<=$C$7,0,H14-$C$7)).
I believe it works fine only if flight starts in the day time and ends at night. Now if the scenario is inverted like if the start time is at night and end time after sunrise how the calculation would be done. I must thank you for your previous help and will remain thankful for your help in future. I do appreciate Excel Forum also, thanks.

Can anyone provide me an excel for calculating day and night flight time using sunrise and sunset