Solved Time Difference (1 Viewer)

[E9-Tech]

I need to track the length of a duty, some duties go beyond the midnight therefore are recorded as 24:15 for a 00:15 and 25:35 for a 01:35 the next morning as the date is referred to the start of the duty.

The StartTime and EndTime fields are currently set as numbers with input mask ‘00:00;0;_’

I need to calculate the time difference between EndTime and StartTime in hh:mm

E.G.
StartTime 20:30 and End Time 23:30 = Duration 03:00
StartTime 20:30 and End Time 24:15 = Duration 04:15

Can I get some help with the calculation to time difference in this scenario!

 

[The_Doc_Man]

The simplest way is to record both time AND date (which WILL fit into Date datatype). Then subtract the date/time values and run the difference through a FORMAT function with "hh:nn" or "hh:nn:ss" as the format template.

Lacking this, you run into the issue of testing whether the start time appears to be AFTER the end time i.e. the difference is negative. In which case, add 1 to the difference.

However, you are going to have one MORE problem. You are using LONG INTEGER as a data type and that ain't going to fly. Time is generally stored in a DATE data type, which is actually a DOUBLE but it is typecast as DATE. Time normally is a FRACTION of a day, which is why your LONG INTEGER won't work. Fractions of a day work fine in DATE data type but won't work in any integer-based data type.

 

[GPGeorge]

The only thing I can add to The_Doc_Man's explanation is that I think people who are less experienced with Access can fail to grasp the difference between the actual stored value of Date/Time fields and the Display Format used with those fields.

In this instance, you are using the Input Mask for force a specific Display Format onto the Long Integers, but that does NOT convert the long integers into anything like an actual Date/Time value. That's really important to understanding why this approach can't succeed for calculating time differences across dates. It's simply putting a mask on a value and pretending it's something else.

 

[azhar2006]

Look at this example. I think this is what I understood from the topic you want.

SELECT tblCustomers.ID, tblCustomers.Cust_Name, tblCustomers.Starttime, tblCustomers.Stoptime, ([Stoptime]-[Starttime])*24 AS [Total hours]
FROM tblCustomers;

 

[E9-Tech]

Thanks for the clarification.
I redesigned the table and have now a date field, I also have the StartTime and EndTime set to time format, I have also abandoned the idea of considering 25:15 format as 01:30 in the morning and have using the normal short time criteria
As i have only a start date I presume that any end time for a specific duty after 00:00 will be finishing the following day.
Is there a way to keep the table with only a StartDate to be imputed and have some calculated end date with some IF solutions?
I hope I explained the concept of keeping the data entry as simple as possible as I have duties that are daily but they can overlap the midnight in certain circumstances.
Thanks

 

[arnelgp]

add another field EndDate, so there is no ambiguity.
therefore the calculation is straightforward:

 

[azhar2006]

Thanks for the clarification.
I redesigned the table and have now a date field, I also have the StartTime and EndTime set to time format, I have also abandoned the idea of considering 25:15 format as 01:30 in the morning and have using the normal short time criteria
As i have only a start date I presume that any end time for a specific duty after 00:00 will be finishing the following day.
Is there a way to keep the table with only a StartDate to be imputed and have some calculated end date with some IF solutions?
I hope I explained the concept of keeping the data entry as simple as possible as I have duties that are daily but they can overlap the midnight in certain circumstances.
Thanks
View attachment 116611

This will be quite convoluted to do in SQL. I believe you will need a helper function in vba

 

[Pat Hartman]

Storing the time value separate from the date always makes calculation more difficult. Take @arnelgp 's advice and add an end date and ALSO get rid of the separate time columns.

Most people, myself included, don't like entering time in a control and the date icon is no help. There are a number of popup forms that you will find posted here and on the web that provide a nice interface for entering a date and a time so the user doesn't have to worry about formatting them as he types.

 

[E9-Tech]

Thanks all for your inputs.
I found a way round it maintaining only 1 date, start and end time field, It may be a bit tedious but serves the purpose.
I reverted back to the standard hour format as the duty latest finish time is 04:00 the following day I used an if statement to add 1 day to an end date field on the form, if the end time falls between 00:00 and 04:00 and with a few time calculations fields I calculate the Elapsed Time.
I attached the db for reference.