Sum Time or durrations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

sI would like to know how to sum time values and display it in short time
format. Example: I have a tech name joe that visited a particular location
twice in onde day. The first visit was 50 minutes long represented by
(12:10:00 AM or 00:50) the second visit was 40 minutes long represented by
(12:40:00 AM or 00:40) for a total of 90 minutes. I would like to sum these
two durrations and end up with 01:30 (1 hour and 30 mnutes). In my table all
the durations are listed vertically with the techs name to the left of each
entry. I need to group by tech and sum all their time/durations; however,
the sum function does not allow me to do this. PLEASE HELPPPPPPPP!!!
 
12:10:00 represents 00:50? How do you figure?

A duration should be stored as a number. 50 minutes is .83333 hours.

If you want to do math on durations, you need to have them in a numerical
format. You can then take the result and turn it into your format indicated
using division.

Do a search on the work "duration" and you should find lots of posts related
to this.

Rick B
 
Thanks for the quick response. Sorry about the typo in my question. First,
lets restate the question....I meant to say:
I would like to know how to sum time values and display it in short time
format. Example: I have a tech name joe that visited a particular location
twice in onde day. The first visit was 50 minutes long represented by
(12:50:00 AM or 00:50) the second visit was 40 minutes long represented by
(12:40:00 AM or 00:40) for a total of 90 minutes. I would like to sum these
two durrations and end up with 01:30 (1 hour and 30 mnutes). In my table all
the durations are listed vertically with the techs name to the left of each
entry. I need to group by tech and sum all their time/durations; however,
the sum function does not allow me to do this. PLEASE HELPPPPPPPP!!!

Everything I found on durations told me how to figure the duration from a
starting point to an end point. Nothing I found told me how to sum two
durations together in an aggregate query. I know how to add time to a time
such as #12:50:00 AM# + #12:40:00 AM# which displays 1:30:00 AM or 01:30 but
I need to know how to some the time in a aggregate query so that I can group
by tech.
 
vzbuster said:
Thanks for the quick response. Sorry about the typo in my question. First,
lets restate the question....I meant to say:
I would like to know how to sum time values and display it in short time
format. Example: I have a tech name joe that visited a particular location
twice in onde day. The first visit was 50 minutes long represented by
(12:50:00 AM or 00:50) the second visit was 40 minutes long represented by
(12:40:00 AM or 00:40) for a total of 90 minutes. I would like to sum these
two durrations and end up with 01:30 (1 hour and 30 mnutes). In my table all
the durations are listed vertically with the techs name to the left of each
entry. I need to group by tech and sum all their time/durations; however,
the sum function does not allow me to do this. PLEASE HELPPPPPPPP!!!

Everything I found on durations told me how to figure the duration from a
starting point to an end point. Nothing I found told me how to sum two
durations together in an aggregate query. I know how to add time to a time
such as #12:50:00 AM# + #12:40:00 AM# which displays 1:30:00 AM or 01:30 but
I need to know how to some the time in a aggregate query so that I can group
by tech.
Click to expand...

The basic problem is while humans might look at the expression "1:30" and
consider it an equally valid way to represent the time 1:30 and the
duration of 1 hour, 30 minutes; to Access this is ALWAYS the time 1:30.

The Date/Time DataType is intended for storing "a point in time" not
"amounts of time". Excel does both so this can be confusing for people
moving to Access. This is why it is always recommended to store durations
as numbers and then use an expression *for display purposes* that looks
like "1:30" after you have aggregated on the numerical values.

Here's another way to look at it. What is Monday plus Thursday? It's a
silly question isn't it? When you ask Access to add #12:30# to #1:50# that
is equally invalid, but since Dates are stored internally as numbers it
works (to a point). As soon as the result exceeds 24 hours though it
starts to break down.
 
Your logic is all wrong here. The following statement does not make any
sense at all:
#12:50:00 AM# + #12:40:00 AM# which displays 1:30:00 AM


You can't ADD TIME. You add durations. You need to store durations as a
number not a time.

Again, do a search for "durations". Non of them should tell you to add two
time fields together to get anything.
 
In response to one of your many posts.

Assuming you have come up with a total duration and the field is called
[TotalDuration], you could display it on your report or form in minutes and
hours, by adding an unbound text box with the control source being...

=[TotalDuration]\60 & Format([TotalDuration] Mod 60, "\:00")
 
Rick,
That make a lot of sense and is pretty much what I figured, the odd thing
is that #12:50:00 AM# + #12:40:00 AM# really does equal 1:30 AM in access.
Paste this into a empty cell in one of your queries in design mode then run
it. You will see the result 1:30 AM.

In any case, how do I convert 64 to look like 01:04 (One hour and four
minutes)?? as I said before I searched for durations and everything I found
told me how to figure the duration from a starting point to an end point.
Nothing told me how to convert 64 to look like 01:04
 
vzbuster said:
Rick,
That make a lot of sense and is pretty much what I figured, the odd thing
is that #12:50:00 AM# + #12:40:00 AM# really does equal 1:30 AM in access.
Paste this into a empty cell in one of your queries in design mode then run
it. You will see the result 1:30 AM.
Click to expand...

Yes it does, but add several more similar values until the result passes
midnight of the next day and the result now looks incorrect.
In any case, how do I convert 64 to look like 01:04 (One hour and four
minutes)??
Click to expand...

Format(64\60,"00") & ":" & Format(64 mod 60,"00")
 
Wow! that seems to work I don't understand this modulus thing, I'm reading
all about it but I don't understand how it works:

supposedly
MyResult = 10 Mod 5 ' Returns 0.
MyResult = 10 Mod 3 ' Returns 1.
MyResult = 12 Mod 4.3 ' Returns 0.
MyResult = 12.6 Mod 5 ' Returns 3.

accoding to the description I'm supposed to divide the 1st number by the
second number number and the result is the remainder. Lets use the second
example
10/3 = 2.52 so the remainder is .52 not 3

Obviously I'm way off base here, can you state this for me in a way that my
stupid brain can understand :) ??



Rick B said:
In response to one of your many posts.

Assuming you have come up with a total duration and the field is called
[TotalDuration], you could display it on your report or form in minutes and
hours, by adding an unbound text box with the control source being...

=[TotalDuration]\60 & Format([TotalDuration] Mod 60, "\:00")






vzbuster said:
Thanks for the quick response. Sorry about the typo in my question. First,
lets restate the question....I meant to say:
I would like to know how to sum time values and display it in short time
format. Example: I have a tech name joe that visited a particular location
twice in onde day. The first visit was 50 minutes long represented by
(12:50:00 AM or 00:50) the second visit was 40 minutes long represented by
(12:40:00 AM or 00:40) for a total of 90 minutes. I would like to sum these
two durrations and end up with 01:30 (1 hour and 30 mnutes). In my table all
the durations are listed vertically with the techs name to the left of each
entry. I need to group by tech and sum all their time/durations; however,
the sum function does not allow me to do this. PLEASE HELPPPPPPPP!!!

Everything I found on durations told me how to figure the duration from a
starting point to an end point. Nothing I found told me how to sum two
durations together in an aggregate query. I know how to add time to a time
such as #12:50:00 AM# + #12:40:00 AM# which displays 1:30:00 AM or 01:30 but
I need to know how to some the time in a aggregate query so that I can group
by tech.
Click to expand...
Click to expand...
 
vzbuster said:
Wow! that seems to work I don't understand this modulus thing, I'm reading
all about it but I don't understand how it works:

supposedly
MyResult = 10 Mod 5 ' Returns 0.
MyResult = 10 Mod 3 ' Returns 1.
MyResult = 12 Mod 4.3 ' Returns 0.
MyResult = 12.6 Mod 5 ' Returns 3.

accoding to the description I'm supposed to divide the 1st number by the
second number number and the result is the remainder. Lets use the second
example
10/3 = 2.52 so the remainder is .52 not 3

Obviously I'm way off base here, can you state this for me in a way that my
stupid brain can understand :) ??
Click to expand...

um...10/3 is NOT 2.52 for starters.

10/3 = 3.3333.... or 3 remainder 1
10\3 = 3
10 mod 3 = 1
 
Rick,
Boy my typing/thinking yesterday was very questionable, obviously 10/2
does not equal 2.52. It's the fourth example 12.6/5 that equals 2.52. What
can I say, I was too deep into programming yesterday and I couldn't think
straight any more. In any case I really appreciate your excelellent help.
After my last posting yesterday, I went home and researched this modulus
thing and now I completely understand it. It's pretty neat and something
I've never heard of before.
One thing still interest me, from what I can tell the backslash and the
zero zero in the format command you used ("\:00") ,which I've now seen on
other websites as well, appear to be unpublished parameters for the format
command. When I look all through the helps screens for the format function
and the format property date/time, I never see the backslash or the zeros
listed. By looking at the result and playing with it, I understand what it
does, but I would like to see the acutual documentation for these parameters.
Perhaps this documentation could teach me some more things.
 
vzbuster said:
Rick,
Boy my typing/thinking yesterday was very questionable, obviously 10/2
does not equal 2.52. It's the fourth example 12.6/5 that equals 2.52. What
can I say, I was too deep into programming yesterday and I couldn't think
straight any more. In any case I really appreciate your excelellent help.
After my last posting yesterday, I went home and researched this modulus
thing and now I completely understand it. It's pretty neat and something
I've never heard of before.
One thing still interest me, from what I can tell the backslash and the
zero zero in the format command you used ("\:00") ,which I've now seen on
other websites as well, appear to be unpublished parameters for the format
command. When I look all through the helps screens for the format function
and the format property date/time, I never see the backslash or the zeros
listed. By looking at the result and playing with it, I understand what it
does, but I would like to see the acutual documentation for these parameters.
Perhaps this documentation could teach me some more things.
Click to expand...

I'm not sure where it's documented, but a zero is a forced digit
placeholder and the "\" simply tells Access to treat the next character in
the format string as a literal character (meaning I want this character in
my output at this spot) rather than as a token that might imply something
else when used in the format string.

For example, within the expression...

Format(123.00123,"#.00")

....The pound sign indicates "display this digit and any additional digits
to the left of this spot (if they exist)" and results in...

123.00

....whereas in this expression...

Format(123.00123,"\#.00")

....the "\" character tells Access "I actually want the pound character in
my output" and results in...

#.00
 
Rick,
Why does 64/60 & Format(64 Mod 60,"\:00") = 1:04 instead of 1.06:04

What happens to the .06? because 64/60 = 1.06

so 1.06 & Format(64 Mod 60,"\:00") should equal 1.06:04
 
Wait I see part of the reason now. it has something to do with the fact that
the first slash is also a back slash and not a normal division sign(forward
slash). I still don't fully understand how the magic works though. what
does 64\60 mean as opposed to 64/60??
 
vzbuster said:
Wait I see part of the reason now. it has something to do with the fact that
the first slash is also a back slash and not a normal division sign(forward
slash). I still don't fully understand how the magic works though. what
does 64\60 mean as opposed to 64/60??
Click to expand...

The \ operator does integer division which truncates any decimal portion in
the result.
 
Back
Top