Calculating Elapsed Time

  • Thread starter Thread starter Machyde
  • Start date Start date
M

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?
 
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
 
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
 
Back
Top