Travel duration taking into account different time zones

[Jayzon]

Departure	Arrival	Duration		From	To	Time zone diff. Departure UTC	Time zone diff. Arrival UTC
24-01-2024 06:00	25-01-2024 01:00	-0.5		Shanghai	Copenhagen	+8	+1

I am trying to calculate trip duration and googling i found a formula, but i would of course like to adjust the formula to cast of the sum total hours spend travelling.
The formula i used is in duration cell D3 Formula : =(((B3-C3)*24)+(H3-I3))/24

Thanks in advance for any support on this

 

[Joe4]

Just a guess, but I think you need to flip columns B and C in your formula, i.e.

=(((C3-B3)*24)+(H3-I3))/24

If that does not give you what you need, please tell us what answer you expect from the values above, and explain the logic of how to arrive at that value.

 

[Jayzon]

Just a guess, but I think you need to flip columns B and C in your formula, i.e.

=(((C3-B3)*24)+(H3-I3))/24

If that does not give you what you need, please tell us what answer you expect from the values above, and explain the logic of how to arrive at that value.

Switching these gives me a result of 1,08 also not what i am looking for. My guess is based on the initial result that the correct sum would be 23,5 hours but i am not sure. The result i am looking for is total sum of hours spent travelling taking into account the different time zones.

 

[Joe4]

Switching these gives me a result of 1,08 also not what i am looking for. My guess is based on the initial result that the correct sum would be 23,5 hours but i am not sure. The result i am looking for is total sum of hours spent travelling taking into account the different time zones.

1.08 seem correct to me, as that is 26 hours (26/24) to get days, which is what your formula is returning.

The difference between the Departure and Arrival times in 19 hours.
But you need to add in an additional 7 hours due to the time zone differences.
19 + 7 = 26.

So it seems correct to me, unless you can demonstrate otherwise.

 

[Jayzon]

1.08 seem correct to me, as that is 26 hours (26/24) to get days, which is what your formula is returning.

The difference between the Departure and Arrival times in 19 hours.
But you need to add in an additional 7 hours due to the time zone differences.
19 + 7 = 26.

So it seems correct to me, unless you can demonstrate otherwise.

I am sure you are right, what i want is for the formula to return is the actual hours spent travelling and not days. Where i picked the formula of google it actually returned hours but was setup to handle the different time zones. So to get this your suggestion would be to just multiply the current result by 24 ?

 

[Joe4]

Just change the format of the cell to a Custom format of:

[h]:mm

and it will return:
26:00

 

[Jayzon]

Just change the format of the cell to a Custom format of:

[h]:mm

and it will return:
26:00

Thank you very much, you have been very helpfull

 

[Joe4]

You are welcome.
Glad I was able to help!