Calculating Elapsed Time

[Machyde]

How do I calculate elapsed time in a simple query when the starting time is
before 00:00 and the end time after 00:00 ... all I'm getting at the moment
is a negative number?

 

[Bruce]

Use the DateDiff function for the two values in question.

 

[vanderghast]

A date time is store as a floating point number, but around 0, there is
something different than the way floating point numbers work.

? Format( -0.25, "long date"), Format( -0.25, "long time")
Saturday, 30 December, 1899 06:00:00

? Format( 0.25, "long date"), Format( 0.25, "long time")
Saturday, 30 December, 1899 06:00:00

So, note that both 0.25 and -0.25 return the same point in time! (even if
the value differ, ie, 0.25 <> -0.25 , if considered as floating point
number).

So, if you have to play with intervals (ie, with date_time values when the
integer part is zero or near zero), be sure to use DatePart and DateDiff,
which are aware of that particularity, rather than using plain arithmetic, +
and -, which behave as floating point number behave, NOT the way date and
time implementation behave.


Vanderghast, Access MVP

 

[John Spencer]

The following expression can be used to calculate elapsed time when there is
no date component and times may be over two days. For example, 11:49 PM to
1:00 AM

Number of Minutes:
(DateDiff("n",Starttime,Endtime) + 1440) Mod 1440

Number of Seconds:
(DateDiff("s",Starttime,Endtime) + 86400) Mod 86400

If the starting time and ending time fields also contain a date component then
you can use DateDiff without the manipulation of the results returned by the
DateDiff function.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County