Guides

Case Study: Flight Delays at Boston Logan in Excel (Real BTS Data)

By Ravindra Bagale, Excel & Data Analytics trainer · LinkedIn · Published: 7 October 2026 · Last updated: 7 October 2026

A Boston company's travel manager is writing a booking policy. The question from the finance team is short: "Which flights out of Boston leave on time?" The answer is in the government's flight-by-flight file. In July 2026, 13,277 flights were scheduled to leave Boston Logan (BOS). Flights scheduled before 9 am left on time 88.7% of the time; flights from 5 pm onward only 45.3%. The departure hour mattered more than the airline.

Download the BTS Reporting Carrier On-Time Performance file for one month, filter Origin to BOS, keep the useful columns, add the airline name with XLOOKUP and the hour with INT(CRSDepTime/100). Use COUNTIFS to count flights, cancellations and on-time departures (DepDel15 = 0) by airline, hour block and day, AVERAGEIFS for the average delay, and SUM for the delay-cause minutes. Chart the results on one dashboard sheet.

Download the practice file (case-study-flight-delays-boston.xlsx)

Excel file, 920 KB. It holds the trimmed real data and every formula used below. It has every BOS departure in July 2026 with 20 columns, the airline lookup, the analysis and the dashboard.

The business question

A late flight costs a business traveller a meeting; a cancelled one can cost a day. The travel manager wants rules, not opinions:

1. Which airlines at Boston Logan leave on time most often, and which cancel most?
2. Does the time of day matter?
3. When flights are late, what is the reason?

The data: source and licence

Step 1: Clean and shape the file

1. Unzip the file and open the CSV with Data > From Text/CSV. It has about 632,000 rows, which fits in Excel but is slow, so click Transform Data to filter in Power Query.
2. Filter Origin to BOS and remove the columns you do not need (keep 18 of the 110).
3. Load to a table called Flights.
4. Make a small table Carriers with the airline code and name from the BTS lookup, and add =XLOOKUP(B2,Carriers[Code],Carriers[Airline]).
5. Add the scheduled hour: =INT(F2/100) turns 1745 into 17.
6. Check: 13,277 rows, dates 1 to 31 July, 786 cancelled and 57 diverted flights. Cancelled flights have no delay minutes, so leave them out of on-time and average-delay formulas.

Step 2: Compare airlines

1. Insert a PivotTable from Flights: Airline in Rows, Flight number in Values (Count), Cancelled in Values (Sum).
2. For a fixed report, use formulas on an Analysis sheet:

Flights            =COUNTIFS(Flights[Airline code],A3,Flights[Cancelled],">=0")
Cancelled          =COUNTIFS(Flights[Airline code],A3,Flights[Cancelled],1)
On-time departures =COUNTIFS(Flights[Airline code],A3,Flights[Cancelled],0,Flights[Departed 15+ min late],0)
On-time %          =G3/F3            (F = flights that operated)
Avg delay (min)    =AVERAGEIFS(Flights[Departure delay (min)],Flights[Airline code],A3,Flights[Cancelled],0)

Among airlines with 500 or more flights, United Air Lines Inc. left on time most often (74.9%) and Southwest Airlines Co. least often (53.4%). JetBlue Airways cancelled the highest share (9.3%). Airlines with fewer than 100 flights (Frontier, Allegiant) are too small to judge from one month. Republic Airline is a regional airline whose flights are usually sold under a larger airline's brand, so check who operates a flight before you book.

Step 3: Compare departure hours

Use the Departure block column (hour blocks set by BTS) with the same COUNTIFS pattern:

The pattern is clear: before 9 am, 88.7% of flights left on time and 4.1% were cancelled; from 5 pm onward, only 45.3% left on time and 10.2% were cancelled. Delays build up during the day, because a late plane makes its next flight late too.

Step 4: Why were flights late?

For the 3,632 flights that arrived 15+ minutes late, the airlines split the delay minutes into five causes. =SUM(Flights[Late aircraft delay]) and so on:

Late-arriving aircraft (the plane came in late from its previous flight) caused the largest share, which matches the hour pattern. Cancellations by reason code: carrier 254, weather 236, National Air System 296, security 0. The worst days were 21 July (139 cancelled of 433), 28 July (98 cancelled of 434), 18 July (85 cancelled of 380).

Step 5: Build the dashboard

1. On the Dashboard sheet add six KPI cards: departures, on-time departures, cancelled, on-time arrivals, average departure delay and the most cancellations in one day (=MAX(...)).
2. Insert a bar chart of on-time departure % by airline and a column chart of on-time % by scheduled hour.
3. Add a pie chart of delay minutes by cause (only five slices, so a pie works) and a column chart of cancellations per day.
4. Turn off gridlines, align the charts and set landscape, fit to one page.

The Dashboard sheet of the practice workbook (real data: U.S. Bureau of Transportation Statistics).

Step 6: The recommendation

1. Book the first flights of the day for important trips. Before 9 am, 88.7% of BOS departures left on time in July 2026, against 45.3% from 5 pm onward.
2. Avoid the last flight of the evening when the trip cannot slip. Evening flights were cancelled about 2.5 times as often as morning flights (10.2% vs 4.1%), and there is no later flight to rebook onto.
3. Use airline data as a tie-breaker, not the main rule. On-time departure rates for the bigger airlines ranged from 53.4% to 74.9%; the hour of the day made a bigger difference than the airline.
4. Re-check every quarter. One summer month with thunderstorms is not a full year. Download a few more months and repeat the same formulas before you make the policy permanent.

What this data cannot tell you

• It covers domestic flights of the airlines that report to BTS, not international flights.
• One month in summer can be very different from winter.
• Delay causes are reported by the airlines using BTS categories; they explain minutes, not the full story of a bad day.