471,573 Members | 1,675 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,573 software developers and data experts.

time functions - total greater than 24 hours

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?
Jun 8 '06 #1
3 15709
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
Jun 8 '06 #2
"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)
Jun 8 '06 #3
"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

Jun 8 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by iceColdFire | last post: by
2 posts views Thread by meltedown | last post: by
1 post views Thread by Consigliare | last post: by
5 posts views Thread by simondsm | last post: by
2 posts views Thread by lucius | last post: by
3 posts views Thread by Generic Usenet Account | last post: by
blackgoat
reply views Thread by blackgoat | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.