Summing up time in a report

[kastaman]

Hi there,

I have a field where the working hours are keyed in like: 6:15 - six hours and fifteen minutes.

I would like to have the ability to sum up the hours for let's say a week like:
6:15
5:30
2:30
1:30
8:15
____
24.00 hours

I tried the datepart expression in the query but I only get either the hours or minutes:
DatePart("h",[LgdInTm])

Thanks in advance,

Kastaman

 

[MichaelRed]

In the land of Ms. Speak, date & time are stored as one thing (a 'floating point' number). In this land, the whole number (integer) part of the data represents "Days" - and is commonly represented as the number of days since the beginning of (Ms.) Time (Dec 30, 1899?), while the fractional (decimal) part is the part of an elapsed day. So, when you "sum" the times, your numbers look something like:

6:15[tab]0.2604
5:30[tab]0.2291
2:30[tab]0.1041
1:30[tab]0.0625
8:15[tab]0.34375
----------------
24:00[tab]1.

If you want to get Hours and minutes seperatly, you need to deal with the summation as two seperate issues. The simple aggregate function should properly sum the data time data types, but it will ALWAYS result in the floating opint number with the whole number as "days" and the fraction as a part of the day. To convert to Hours and fractions of hours, multiply by 24. As is easily observed in your example, the summation (one "1&quot is exactly the 24 hours.


A more 'chalenging example might have 'sum' of 1.6365721 "Hours", representing approximatly 39:17. This is derived from:

? 1.6365721 * 24
39.2777304

meaning 39.277 Hours

Take the fractional Hour (0.277.04) and multipluy by 60 (Min / Hr):

? .2777304 * 60
16.663824
to arrive at the ~~ 17Min

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WildHare]

I think what [red]Michael[/color] was trying to say, in his own inimitable yet correct way, was that, in an Access database, a TIME datatype with a value of 6:15 is NOT, repeate NOT 6 hours and 15 minutes, but 6 hours and 15 minutes past midnight.

A date/time datatype is used to capture an EXACT MOMENT IN TIME, not a measure of elapsed time.

Think of the DATE/TIME datatype as a factory time clock. When you come in in the morning, you punch your card and pull it out - you don't leave it in there the full eight hours, do you? So don't store "time worked" as a date/time datatype.

Calculations involving ELAPSED date/time characteristics (Years, Quarters, Months, Weeks, Days, Hours, Minutes, Seconds, Milliseconds) should be initialized as NUMBERS, not as DATE/TIME variables.



Remember, you're unique - - just like everyone else
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[kastaman]

Thanks to both for in-depth details regarding time as it relates to Access.

Does this mean I'll need to have 2 unbound boxes to determine the hours and minutes then add the boxes together?

Kastaman Thanks in advance,

Kastaman

 

[WildHare]

That's one way. Make the boxes NUMBER type, not DATE type. You can always edit-check them to make sure the hour guy is less than 25 and the Minutes guy is less than 61. Or something like that...

Add up all the hours first and then add up the minutes and div by 60 to get a total ELAPSED time.

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com