I’m trying to create a formula to calculate elapsed time in a flight. I have a departure and arrival day/time, and UTC table with airport code and UTC, I followed this post but it seems not working, since the post is very old, so I decided to ask thie question again here. Can someone help me to figure out what the formula would be?
ps: in the post, it seems the result of calculation ended up in decimal (instead of 0:30 min, the result was 0.5, and the replier multiplied by 24 to end up in HH:MM, can someone explains to me why? I’ve attached my sample spreadsheet to show it.
trip2.ods (15.0 KB)
You didn’t need to multiply by 24, you only needed to format the result using something like [HH]:MM. This is because in Calc (and in Excel, by the way), a date/time is a fraction of a day, so 12 hours is half (0.5) of a day.
What specifically in the StackOverflow answer doesn’t work for you? The “post is very old” is not a problem, it works all the same.
The formula in J4, for instance, would be like
=F4-D4+(VLOOKUP(B4;$L$3:$N$12;3;0)-VLOOKUP(C4;$L$3:$N$12;3;0))/24Thanks for your prompt reply, what I don’t understand is
- for the trip BCN-VIE, it’s the same time zone, and if calculated by hand, the difference will be 2:15, but the formula gave 2:14
- for the trip VIE-TPE, it’s different timezone, and the duration should be 13:30, but the formula gave 0:48 (UTC does not take into account summer/winter time, so the difference should be only 1 hrs, but after calculation, the difference is 3 hrs.
ps: my result cell is already formatted to time in HH:MM, but the result does not match with expected value.
you need the duration so format [HH]:MM
Did you follow the following?
The square brackets give you the duration time format, which rounds (as opposed to the wall clock time format, which truncates).
In addition to the above (if you used the proper duration format, you would get 24:48, not 0:48), did you pay attention to the order of negation - you must subtract the timezone of destination from the timezone of departure, not the other way round.
Please acknowledge that reading and following is important, and that not doing that, you just waste time - both yours, and of those who try to help.
Thanks for this tip, I saw my mistakes now, thanks again for all of you for helping me out.