The solution for gate management

[airlinemanage]

  success timetable AE.xlsx (Size: 9.63 KB / Downloads: 6)
  success timetable AE.xlsx (Size: 9.63 KB / Downloads: 6)
  success timetable AE.xlsx (Size: 9.63 KB / Downloads: 6) As I showed before, you are able to put data to excel from the route database. Now, I will attach two excel files to manage gates. Open success timetable AE.xlsx, you'll see column A to F have data. Overwrite the data there with yours, then press Sort in DATA tab. In the Sort window, you should set Sort by (Column F), Then by (Column D), so that all rows related to the same aircraft are packed together, and are arranged in order based on time. 

Press I2 cell, change the formula: =IF(ISNUMBER(SEARCH("— PEK",$A2)),$D2,IF(AND(ISNUMBER(SEARCH("PEK —",$A2)),$F2<>$F1),2/24,"")),
Change PEK to the airport you want to take a look  at. Apply the new formula to the whole column.

Copy the result of column I and J, you must copy them by value only. Sort them based on column I, and copy the result to column B and C of AirlineEnterprise_gantt_chart.xlsx, zoom out and you can see the whole day with red bars indicating there is an aircraft parking at the airport at that time.

Note that if an aircraft arrives at an airport at the airport and leaves the next day, it is split to two rows. For example, an aircraft lands at 22:00 and leaves at 7:00 on the next day, instead of 22:00-7:00, the system splits it to 22:00-2:00 and 2:00-7:00.

 (this chart is the ideal case. In fact, my airline has pilots who always forgot to work , so aircraft stays in an airport in unexpected time. To get enough gates, you'd better lease more than what the excel chart shows.

---

The other file is here.

[airlinemanage]

Just noticed a small error, column I is the ready time not the arrival time of an aircraft. The difference is ready time is the time after an aircraft completes the turnaround step. So, in order to get the arrival time, you should type in the turnaround time in column H. Modify I2 formula to:
=IF(ISNUMBER(SEARCH("— PEK",$A2)),$D2-$H2,IF(AND(ISNUMBER(SEARCH("PEK —",$A2)),$F2<>$F1),2/24,""))


and apply the formula to the whole column, then you'll notice that column I has the arrival time.