Expression Builder for time subtraction help

[abristow]

Hello,

I am not sure if this is the correct sub-forum for this post, but it seemed the most relevant of them.

I am trying to subtract two columns with time values in 24 hour time format. Column A being "Launch Time" , Column B being "Land Time" and create a calctulated field.

I am having some issues though when the Land Time is "less than" the Launch Time.

For example: Launch time = 19:45 and Land Time = 02:05 the result it gives me when usign the expression "LAND TIME" - "LAUNCH TIME" is 17:40 when my desired result would be 06:20.

I have also tried using an iif statemtn of:

iif (Land time) < (Launch Time), (Launch Time) - (Land Time), (Land Time) - (Launch Time) but it still gives me the same results as the above expression.

I am not sure why it won't calculate Launch time = 19:45 and Land Time = 02:05 the result it gives me when usign the expression "LAND TIME" - "LAUNCH TIME" is 17:40
as -17:40 either.

Any insights on how to accuratley get the flight duration times between these two fields? The issues come from when a flight takes off at lets say 1900 on March 2nd but lands at 0500 on March 3rd. The dates for the data set are in seperate columns which are listed as "Launch Date" and "Land Date"

 

[Minty]

I am not sure why it won't calculate Launch time = 19:45 and Land Time = 02:05 the result it gives me when usign the expression "LAND TIME" - "LAUNCH TIME" is 17:40
as -17:40 either.

Any insights on how to accurately get the flight duration times between these two fields? The issues come from when a flight takes off at lets say 1900 on March 2nd but lands at 0500 on March 3rd. The dates for the data set are in separate columns which are listed as "Launch Date" and "Land Date"

The last part of your post is critical. If you are importing these values then your times are coming in with a default date of 31/12/1899 or similar. Thus your calculations are actually 31/12/1899 19:45 - 31/12/1899 05:00

See the problem?

So you need to add your time fields to your date fields, to get the correct datetime value, then do your calculations.

 

[abristow]

The last part of your post is critical. If you are importing these values then your times are coming in with a default date of 31/12/1899 or similar. Thus your calculations are actually 31/12/1899 19:45 - 31/12/1899 05:00

See the problem?

So you need to add your time fields to your date fields, to get the correct datetime value, then do your calculations.

The issue is that this data comes from various organizations with a standardized data sheet they fill out. I then copy and paste all their data from the excel sheet into an excel sheet with a macro enabled formatting button. From there I copy and paste it into Access which essentially mirriors the format of the excel sheets they fill out.

I am able to easily use the function "=MOD(Land TIme - Launch Time, -1) in excel to achieve the same result, but I'd rather have it perform that function in Access.

Any insights on if that is possible? WIth over 10,000 records of data I have no idea how I would be able to merge the date and time fields for the entire database. I am unable to change the excel sheets the external organizations use becaue they are the standardized ones our parent organization has rolled out. This is why I have to use another excel sheet to format the data correctly.

 

[Minty]

Simply do it in a query. Assuming your fields are FlightDate and FlightTime something like;

NewProperDate: FlightDate + FlightTime

This takes advantage of the fact that Access stores datetime values as decimal numbers, the integer references the date, the fraction the time portion so;
1 = #31/12/1899 00:00#
43895.403287037 = 05/03/2020 09:40:44

Does this help?

 

[abristow]

Simply do it in a query. Assuming your fields are FlightDate and FlightTime something like;

NewProperDate: FlightDate + FlightTime

This takes advantage of the fact that Access stores datetime values as decimal numbers, the integer references the date, the fraction the time portion so;
1 = #31/12/1899 00:00#
43895.403287037 = 05/03/2020 09:40:44

Does this help?

I understand what your saying but would this require me to run the query everytime new data is added to the DB?

I've tried running a query that looks similiar to:

SELECT IF (Land Time < Launch Time, Launch Time - Land Time, Land Time - Launch Time)
From "Table"

but this does not work.

I also noticed though that when I run the query

SELECT Land Time - Launch Time
From Table

It gives me the positive decimal time foramts for when the land time is larger than the launch time, and also gives me the negative decimals for when the land time is smaller than the launch time.

When using the expression finder though it doesn't make that negative time value so I thought that was strange how one process in Access eliminates the negative value and replaces it with a positive one automatically...

 

[Minty]

I understand what your saying but would this require me to run the query everytime new data is added to the DB?

Yes but you could incorporate it into your import routine.
As a one-off exercise for your existing data, it would be a simple task to add the new fields (LaunchDateTIme & LandedDateTime) then run a one time update query to put the correct summed values in. You don't need to do this as you can always sum the values to give the correct data, but it might simplify your life later.

SELECT IF (Land Time < Launch Time, Launch Time - Land Time, Land Time - Launch Time)
From "Table"

but this does not work.

It's a pointless calculation without the date part included as you have discovered. Access doesn't know if 05:00 is later or earlier that 14:00 if there is a different date involved, neither would you