0

I have a start time and and an end time and would like to calculate the duration expended between the two. This is very simple to do when the start and end time's are the same time zone, like in the below flight example

enter image description here

Some points

  1. Departure formatted to YYYY-MM-DD
  2. Arrival formatted to YYYY-MM-DD
  3. Duration calculated like so =IF(NOT(AND(ISBLANK(D2),ISBLANK(C2))),MOD(D2-C2,1)*24,0)

Now if I introduce time zones and try to carry out the same calculations, the equation obviously fails, and to be completely honest, I don't even know how to begin the calculation to try to make it work.

enter image description here

Some Points

  1. Departure UTC is set to General format
  2. Arrival UTC is set to General format

Could someone provide a minimal working example (MWE) of an equation which will calculate the flight duration based on local departure and arrival times.

Improve this question
3
  • @JonathanvonSchroeder to be honest, I can't wrap my head around it. I think, however, if you are on a 25 hour flight, it will count it as 1 hour
    – puk
    Commented Feb 18, 2017 at 17:32
  • @JonathanvonSchroeder point taken. I will remove that line as it complicates the question for no reason
    – puk
    Commented Feb 18, 2017 at 17:39
  • This won't cover the previous comments issue, but you could as a first step convert the departure time to the time zone of the arrival.. or vv if you prefer.
    – Solar Mike
    Commented Feb 18, 2017 at 17:42

1 Answer 1

Reset to default
1

You have to include the date in both departure and arrival time. The formula would then be something like: =((arr.time-dep.time)*24)+(arr.timezone-dep.timezone)

In your case, with the time zones:

=(((D6-C6)*24)+(H6-I6))/24

Updated screenshot

This requires that you input the departure and arrival times with date and time. I tried it out, and it seems to work for negative and positive time zones.

Edit: I added /24 and enclosed the preceding expression in parentheses to get the calculation in hh:ss format.

Improve this answer
4
  • I think you have it the wrong way around, should be =((arr.time-dep.time)*24)+(arr.timezone-dep.timezone), but in any case, it fails for cases of flights arriving +1 days, try this flight, will give you a time of 27.75 instead of 9.75 google.ca/flights/…
    – puk
    Commented Feb 18, 2017 at 18:57
  • Sorry, you have it right. The formula was correct, but I got the writeout the wrong way round. I'll look at the example you mentioned. My response has been edited.
    – LarsS
    Commented Feb 18, 2017 at 19:29
  • As far as I could see, when you input the correct time zones for the flight you linked (Vancouver -8, Frankfurt +1), you get 9,75 hours, or 09:45.
    – LarsS
    Commented Feb 18, 2017 at 19:37
  • Yep, the formula is correct. And to convert the result to hours:minutes, divide the expression by 24.
    – LarsS
    Commented Feb 18, 2017 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.