Using MS Access/Elapsed time

Advertisement


Question
Hi Scott
I have a database which records start time and end time in a table.
What I want to do is be able to calculate the elapsed time between the start and end times.
No problem if they are both on the same day however, if the start time is on one day and the end time is either the next day, or later, then I have a problem.
If I use the general date format I have no problem but I would like to be able to insert the date in one field and the time in another.
I suspect that I could concatenate the date and the time for the start and end and work from there, however I don't really understand how to go about this.
Could you help please?

Answer
Why do you prefer to keep the date and time in separate fields? The problem you are encountering is a common one and is the reason I recommend keeping them together. The general date format only governs how the value is displayed, not how its stored. A date value is actually stored as a double precision number where the integer portion represents the number of days since 12/30/1899 and the decimal portion a fraction of a day (i.e. .25 = 6 AM or 6 hrs). That is why, when you subtract one time from another or use the DateDiff function, it doesn't work right if there is no date element.

For example: a start time of 8PM and an end time 4 AM returns .66667 or a -16 hours. However, 8PM on 5/28 and 4AM on 5/29 returns .3333 or 8 hours.

If you want to DISPLAY the date and time separately you can certainly do that by using 2 controls, one formatted as a date and the other formatted as a time.

If you need to do elapsed time where the time spans multiple days, then you really need to use DateDiff(). So the expression:

DateDiff("n",Startdate, EndDate)

will return the number of minutes between the 2 times. If you need to display this value in a format like 1 Day 2 Hours 15 Minutes. or even 26:15. You can do that using a function that returns a string expression. This can be handled by taking the number of minutes, first dividing by 1440 (the # of minutes in a day) to get the days, then the remainder by 60 to get the hours and using the MOD operator to get the remaining minutes. If you need precision to the second, then you use s instead of n in your DateDiff and change the math accordingly.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.