How to Calculate Flight Time Manually in Excel

By SpreadCheaters
Page last updated: 27/06/2023 | Next review date: 27/06/2025
close

Author bio


SpreadCheaters
View More north_east

In today’s busy world, people travel by plane to save valuable time. Therefore, Flight time estimation allows people to plan their travel itineraries effectively.  By knowing the flight time, we can determine when we need to depart and arrive at the airport, and plan our activities at our destination accordingly. Knowing the flight time, people can manage their valuable time more efficiently.

In today’s tutorial, we’ll learn how to calculate Flight time in Microsoft Excel. Let’s consider the following cases for calculating flight times:

CASE 1 – Domestic Flights

Let’s consider the following dataset that contains details of five domestic flights along with departure and arrival locations and times. The columns of Departure and Arrival Times are already in time format.

STEP 1 – Create a column for Total Flight Time

  • Move your cursor to the topmost cell of the next column.
  • Give the heading to the column for “Total Flight Time”

STEP 2 – Use the Subtraction Formula for Time

  • Create an appropriate formula for the subtraction of the departure time from the arrival time.
  • In this case, the formula is  =F5-D5.
  • Write the formula in the “Total flight time” column of Flight 1.
  • Press Enter key.
  • The total flight time for Flight 1 will be calculated.

STEP 3 – Use Fill Handle to calculate flight time for all flights

  • Move your cursor to the cell that contains the flight time of Flight 1 and select the cell.
  • Move your cursor to the bottom right of the cell.
  • Drag the fill handle to Flight 5.
  • The Flight time for all flights will be calculated.

CASE 2 – Calculating Flight Times for International Flights

In the case of International Flights, there is usually a difference in time zones between departure and arrival locations. So, the method to calculate Flight time for International flights is quite different. Let’s consider the following dataset that contains details of five international flights:

STEP 1 – Determine the time zones of departure locations

  • Find out the time zones of departure locations from www.google.com or any other search engine.
  • Write the time zones of the departure locations in front of the respective locations.
  • In this case, all flights are directed from “New York City, USA”, So the time zones of departure locations are the same (EDT).

STEP 2 – Calculate the difference between  each time zone and GMT(Greenwich Mean Time)

  • Find out the difference between each time zone and GMT(Greenwich Mean Time).
  • Write the difference between each time zone and GMT (In hours) in another column.
  • In this case, the timezone of New York City is EDT(Eastern Daylight Time) and the difference between EDT and GMT is 4 hours. So, we’ll write only 4 in the whole column.

STEP 3 – Convert departure time into GMT

  • Create a new column with the heading “Departure Time in GMT”.
  • To convert departure time into GMT, we’ll add 4 hours from EDT to convert it into GMT i.e. =D2+TIME(E2, minutes, hours). Here D2 is the cell that contains the departure time, and E2 is the cell that contains the difference from GMT in terms of hours. 
  • It is essential to use the TIME function while adding or subtracting hours, minutes, or seconds to time values, otherwise, the result will not be correct.
  • Write this formula in the first cell of the “Difference from GMT” column.
  • Drag the Fill handle to the last row to convert all departure times into GMT, as shown below.

STEP 4 – Determine Time Zones of Arrival locations

  • Find the time zones of Arrival locations from www.google.com or any other search engine.
  • Write the time zones of the Arrival locations in the column.

STEP 5 – Calculate the difference between each time zone from GMT(Greenwich Mean Time)

  • First, find out the difference between each timezone from GMT.
  • Then write the difference between each time zone from GMT(In hours) in another column.
  • In this case, the difference in time zones from GMT is written in column J.

STEP 6 – Convert Arrival time into GMT

  • Create a new column named  “Arrival time in GMT”.
  • To convert arrival time into GMT, we’ll add or subtract (whichever is applicable) the Time difference between the respective time zone and GMT, from Arrival Time. 
  • In this case, the time difference is subtracted as all time zones are ahead of GMT .i.e =I2-TIME(J2,00,00).
  • Here I2 is the Arrival Time and J2 is the time zone difference from GMT in terms of hours.
  • Drag the AutoFill handle to convert all arrival times into GMT.

STEP 7 – Calculate the Total Flight Time 

  • Create a new column named “Total Flight Time”.
  • We can calculate total flight time using the IF function.i.e =IF(logical_test condition,value_if_true,value_if_false).
  • In this case, we’ll use the formula =IF(K2>F2, K2-F2,1+K2-F2).
  • K2 is the cell containing “Arrival Time in GMT” and F2 is the cell containing “Departure Time in GMT”.
  • Write the formula in the “Total flight time column”.
  • Drag the AutoFill Handle to calculate Flight Time for all flights.

Break-Down of the used formula

We have used the formula =IF(K2>F2, K2-F2,1+K2-F2), basically this formula takes care of the fact that negative dates don’t make any sense in Excel. 

While subtracting time in Excel, if we subtract a larger time from a smaller time, then it gives an error like ######. Excel generates this kind of error when the resultant value is either negative or too large. To prevent this error, we use the IF function to subtract time.  In this case, in  “logical_test_condition”, we’ll write K2>F2 i.e. Arrival time greater than the Departure time. In “value_if_true”, we have to write the command that will be executed if the condition is true. So here we’ll write K2-F2 because if the arrival time is greater than the departure time, we simply need to subtract the departure time from the arrival time to calculate Flight Time. But if we apply the same method when the departure time is greater than the arrival time, we’ll get erratic values. In “value_if_false”, we have to write the command that will be executed if the condition is false. So here we’ll write 1+K2-F2, to get the correct answer.