Day/Night Hours Calculation For Pilot's Flight Time w/ Time Zone Consideration

[tonym99]

Hello,

Similar to this solution, I am looking for a slight modification for pilot's time and I can't figure it out:
http://www.mrexcel.com/forum/excel-questions/662103-day-night-shift-calculation.html

Here are the rows and columns I have:

[TABLE="width: 566"]
<colgroup><col span="3"><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]From[/TD]
[TD]To[/TD]
[TD]Legs[/TD]
[TD]Dep[/TD]
[TD]Arr[/TD]
[TD]Duration[/TD]
[TD]Day[/TD]
[TD]Night[/TD]
[/TR]
[TR]
[TD]JFK[/TD]
[TD]IAD[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7:15 AM[/TD]
[TD="align: right"]8:24 AM[/TD]
[TD="align: right"]1:09[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IAD[/TD]
[TD]JFK[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9:05 AM[/TD]
[TD="align: right"]10:15 AM[/TD]
[TD="align: right"]1:10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JFK[/TD]
[TD]HOU[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1:17 PM[/TD]
[TD="align: right"]4:12 PM[/TD]
[TD="align: right"]3:55[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HOU[/TD]
[TD]JFK[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]11:20 AM[/TD]
[TD="align: right"]3:20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JFK[/TD]
[TD]MCO[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2:09 PM[/TD]
[TD="align: right"]4:47 PM[/TD]
[TD="align: right"]2:38[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MCO[/TD]
[TD]BOS[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5:52 PM[/TD]
[TD="align: right"]8:40 PM[/TD]
[TD="align: right"]2:48[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOS[/TD]
[TD]DEN[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9:20 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]4:40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEN[/TD]
[TD]BOS[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12:10 AM[/TD]
[TD="align: right"]5:27 AM[/TD]
[TD="align: right"]3:17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I need something like this very similar for my pilot logbook spreadsheet. The only difference is that my total time is already calculated for me and I'm able to import into the spreadsheet along with the start and end times for my flights. Here's the catch, my total time takes into account any time change that took place from takeoff and landing. So, therefore, the total time might only show 1 hr flight time when you look at the takeoff and landing time and it's 2 hours difference due a time change from say Central time zone to Eastern time zone. Therefore I don't use the formula for total time like you have in the example above (D2).

So, how would you need to change up the formula in order to compensate for time zone changes? Because there are many times when I cross time zones so the Dep and Arr times are reflective of local times and not total duration of the flight.

Have I confused you yet? Lol. Let me know if you might have a solution. I can email you the spreadsheet so you can possibly help me out. I truly appreciate it!!!

Tony

 

[GerryZ]

Maybe:

Let me know!

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:14pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:59.2px;" /><col style="width:50.4px;" /><col style="width:51.2px;" /><col style="width:52px;" /><col style="width:74.4px;" /><col style="width:72.8px;" /><col style="width:56px;" /><col style="width:59.2px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td > </td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">F</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">G</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">H</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style=" border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">From</td><td style=" border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">To</td><td style=" border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">Legs</td><td style=" border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">Dep</td><td style=" border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">Arr</td><td style="background-color:#ffff00; border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">Duration</td><td style="background-color:#008080; color:#ffffff; border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">Day</td><td style="background-color:#ff0000; color:#ffffff; border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">Night</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">JFK</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">IAD</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">7:15</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">8:24</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1:09</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1:09</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0:00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">IAD</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">JFK</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">9:05</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">10:15</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1:10</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1:10</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0:00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">JFK</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">HOU</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">13:17</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">16:12</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2:55</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2:55</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0:00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">HOU</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">JFK</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">7:00</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">11:20</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">4:20</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">4:20</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0:00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">JFK</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">MCO</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">14:09</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">16:47</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2:38</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2:38</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0:00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">MCO</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">BOS</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">17:00</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">3:40</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">10:40</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">7:00</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">3:40</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">BOS</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">DEN</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">21:20</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1:00</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">3:40</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2:40</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1:00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">DEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">BOS</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0:10</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">5:27</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">5:17</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0:00</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">5:17</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F2</td><td >=MOD(E2-D2,1)</td></tr><tr><td >G2</td><td >=F2-H2</td></tr><tr><td >H2</td><td >=(MOD<span style=' color:008000; '>(E2-D2,1)</span>*24-<span style=' color:008000; '>(E2<D2)</span>*<span style=' color:008000; '>(24-6)</span>+MEDIAN<span style=' color:008000; '>(6,24,D2*24)</span>-MEDIAN<span style=' color:008000; '>(6,24,E2*24)</span>)/24</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[tonym99]

Gerry,

Thank you so much for the suggestion. There's still the issue of time zone miscalculations. For example, the last line #9 DEN-BOS isn't correct. Denver is on mountain time and Boston is on Eastern time. There is a two hour difference between the two cities. So, therefore, the flight actually was 3:17 but it's being calculated at 5:17 not taking into account the 2 hour time difference.

I have 1700 lines of flights in my spreadsheet. All of them have the correct total number in the duration column. I just need to make sure that the day/night split numbers equal the duration number. I'm baffled as to how to address this. Should I be maybe referencing the airport identifiers somehow by their time zones? I don't even know how I'd really do that....

Thanks,
Tony

 

[GerryZ]

Hello
sorry for my english is not my first Language
I tried but still have problem with too short flight
I have problem to Know how many hours are night or day shift because expecially when is accroos midnight.You need to know that when you work with the time you have to subtract the biggest number minus the smallest number and the result have to be positive number, but when you have short flight and subtract the Jetlegs in beetween midnight is very commun to have a negative number you get back an error like this #################################### infinity
the best think to do is put another helper column write down the Jet leg a subtract from the total hours and not from the partial time Day/night
so in that case you will never know how many hours day or night
Ciao

 

[AhoyNC]

Tony,
Not sure if this will help, but the web site below has a free download (airports.dat) which has airport ID and hours offset from UTC. You could download into something like Notepad and save as a csv that Excel can read. With a function like VLOOKUP or MATCH you might be able to find and add columns for UTC Dep & Arr.
OpenFlights: Airport and airline data

 

[tonym99]

We actually only fly to 88 different cities, so I was wondering if I could just reference time zones for each airport we fly to and have that referenced somehow?

Tony

 

[AhoyNC]

You should be able to do something like that. If you download the file above into Excel you can then filter down to US and the airports you fly to.
The data below is where I filtered out 3 airports. The 3rd column from the right shows the offset to UTC time. Maybe you could use this to standardize your times.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	3448	General Edward Lawrence Logan Intl	Boston	United States	BOS	KBOS	42.36435	-71.0052	19	-5	A	America/New_York
2	3751	Denver Intl	Denver	United States	DEN	KDEN	39.86166	-104.673	5431	-7	A	America/Denver
3	3797	John F Kennedy Intl	New York	United States	JFK	KJFK	40.63975	-73.7789	13	-5	A	America/New_York
Sheet