A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating Elapsed Time



 
 
Thread Tools Display Modes
  #1  
Old May 20th, 2010, 12:46 PM posted to microsoft.public.access.queries
Machyde
external usenet poster
 
Posts: 1
Default Calculating Elapsed Time

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?
  #2  
Old May 20th, 2010, 12:57 PM posted to microsoft.public.access.queries
Bruce
external usenet poster
 
Posts: 374
Default Calculating Elapsed Time

Use the DateDiff function for the two values in question.
--
Bruce


"Machyde" wrote:

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?

  #3  
Old May 20th, 2010, 01:48 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Calculating Elapsed Time

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


"Machyde" wrote in message
...
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?


  #4  
Old May 20th, 2010, 02:05 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Calculating Elapsed Time

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

Machyde wrote:
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?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:36 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.