time functions - total greater than 24 hours

[Stephen Chaplin]

I'm summing up time spent on jobs over a week, some of these jobs last
greater than 24 hours and when access sums these up it appears that it
starts again at 00:00 once it gets past 23:59.

Is it possible to display the time taken in hours and mins past the 24 hour
clock? i.e 34:23?

[Rick Brandt]

Stephen Chaplin wrote:

I'm summing up time spent on jobs over a week, some of these jobs last
greater than 24 hours and when access sums these up it appears that it
starts again at 00:00 once it gets past 23:59.

Is it possible to display the time taken in hours and mins past the
24 hour clock? i.e 34:23?

The DateTime DataType in most all databases is intended for storing a point in
time, not a duration.

Two things lead to the confusion you are having. People use the written
expression 4:30 to mean both the time four-thirty and the duration four hours
and thirty minutes. In a database it always means the time four-thirty.

Many database engines store DateTimes under the covers as numbers so if you do
mathematical calculations on them they *appear* to work as durations. However
as you have seen, once you cross a 24 hour boundary the date portion changes and
the time portion wraps around.

The proper way to deal with durations is to store the smallest time interval
precision you care about as a number. A table tracking labor hours would
typically store this as the total number of minutes. After all summing has been
accomplished on the numeric value you can then use mathematical expressions to
convert this to hours and minutes for display purposes.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[JBuckner]

"Stephen Chaplin" <sc**@york.ac.uk> wrote in message
news:e6**********@pump1.york.ac.uk...

I'm summing up time spent on jobs over a week, some of these jobs last
greater than 24 hours and when access sums these up it appears that it
starts again at 00:00 once it gets past 23:59.

Is it possible to display the time taken in hours and mins past the 24 hour clock? i.e 34:23?

This is kind of cumbersome but I have used the following calculated fields
in a query to first determine the elapsed minutes between two date/time
intervals and then to convert the minutes to an hours:minutes result:

Table Fields: Date1 (Date/Time), Date2 (Date/Time)

First Calculated Field:
ElapsedMins: Abs(DateDiff("n",[Date2],[Date1]))

Second Calculated Field:
Duration: [ElapsedMins]\60 & Format([ElapsedMins] Mod 60,"\:00")

Example:
Date1 - 4/4/2006 6:34:00 PM
Date2 - 4/3/2006 1:32:00 PM

ElapsedMins = 1742

Duration = 29:02 (29 hours, 2 minutes)

[Bob Quintal]

"Stephen Chaplin" <sc**@york.ac.uk> wrote in
news:e6**********@pump1.york.ac.uk:

I'm summing up time spent on jobs over a week, some of these
jobs last greater than 24 hours and when access sums these up
it appears that it starts again at 00:00 once it gets past
23:59.

Is it possible to display the time taken in hours and mins
past the 24 hour clock? i.e 34:23?

When you use the datediff() function to obtain the interval
between two times (start and stop) the result is a long integer.
Using a date format to display it will often, as in your case,
show garbage.

with datediff(), you can choose seconds, minutes, hours, days,
weeks, months or years. use the one that gives you the precision
you need,

Just write a custom function that converts the long integer for
minutes to a usable format. I posted an example called Sec2Dur()
a couple of years ago, which can retrieved by searching Google
Groups for that name. As written it will convert seconds into
the format hhhhhhhhhh:mm:ss

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com