Need help with Calculating Time difference when working with UTC Timezones in Access

[Course]

I have a database with three tables. Tbl 1 = Airlines, Tbl 2 = Destinations and Tbl 3 = Flights

The Flights table has four fields and is a many to many table. The fields are called Airline-Destination-Departure-Arrival. Airlines is a FK from the Airlines Table and Destinations is a Fk from the Destinations table.
The table records Airlines departure/arrival times on different routes.

I want to record the duration of each recorded flight.

I thought of adding a calculated field to the Flights Table and using it to subtract the departure time from the arrival time.

The problem is the Arrival time is the local time of the Destination and this can include UTC offsets such as UTC+2.
e.g if i fly from London to Helsinki the flight takes 3hrs. If I depart at 11:00am the time in London will be 2pm when I arrive but it will be 4pm local time as Finland is 2hrs ahead of London(UTC+2). If I was to use my calculated field using the local arrival time It would return an incorrect flight duration of 5hrs.

Another problem is that the UTC time differs in Winter by -1 and summer time by +1 in some countrys.

How can I set up my table to calculate the Flight duration correctly.

Thank you

 

[sijpie]

How are the times entered in the table? as excel times (13:05) or with the UTC as well (13:05 UTC+2)?
How many destinations do you have, because you could add a table with destinations (airport codes) and their timezones, then your formula could look up the timezon difference and add or subtract as required

 

[Micron]

On a form, you should enter the date and time for departure in txtDepart and for arrival in txtArrive. It might be more attractive to you to use the built-in calendar to allow date picking, a combo with 24 hours and a combo for 00 to 59 minutes. Another form textbox can be set to equal a custom function you create in a standard module. In that function, you would caluculate the elapsed time using the DateDiff function. I think you also need a list of countries in tblCountries with the UTC shift value as well as a field to store the winter/summer shift flag (Y/N). In this custom function you use the DatePart function to calculate which quarter you are in, translate that to summer/winter and according to the flag, you add/subtract 1 or not. Having said all that, you could also consider having a means to pull in a web page like this one
Travel Time Calculator
Disclaimer: I have no affiliation with that web service and only came accross it a few nights ago because I wanted to calculate my own impending flight time using actual departure/arrival times. Other sites base the calculation on an average speed that you can adjust, which is not what I wanted.

 

[Course]

I solved this by adding a UTC field to the Destinations table which shows the time difference between the destination and the Departure airport. The time difference will stay the same if daylight savings time is adjusted in both the departure airport and destination. The only problem with this is if daylight savings time is applied to only the departure or the destination airport. I have yet to check if this does occur so it could be a problem. Apart from this the solution works.

I then created a query which included the Destination,Departure,UTC and Arrival fields from the tables name in the first post.

I then added a calculated field to the Query called Duration:[Arrival]-[Departure]-[UTC]/24 and formatted this field by right clicking and then selecting properties. I set the format as hh\h:nn\m

 

[mole999]

I don't think Daylight savings are applied simultaneously around the world, even if they were that would be 24 times in each of the change over days, maybe through in a marker that can just say for this range of dates daylight savings will occur

 

[Micron]

I'm curious as to why you're dividing the UTC by 24 (e.g. -2/24)