By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

Sum of times off by 2 minutes

P: n/a
I've got three columns i'm working on: Time Start (Date and Time), Time
End (Date and Time), Total Task Time (Short Time)

I've got Total Task Time working correctly. Here is my query:
SELECT Format(Sum([Total Task Time]),"hh:mm:ss")
FROM Task_LogTest;

However, I manually added it up (I've got 4 test rows) and the query is
returning a time that is off by 2 minutes exactly (my result + 2 min).
Any ideas on why this is happening?

Dec 15 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Drew wrote:
I've got three columns i'm working on: Time Start (Date and Time), Time
End (Date and Time), Total Task Time (Short Time)

I've got Total Task Time working correctly. Here is my query:
SELECT Format(Sum([Total Task Time]),"hh:mm:ss")
FROM Task_LogTest;

However, I manually added it up (I've got 4 test rows) and the query is
returning a time that is off by 2 minutes exactly (my result + 2 min).
Any ideas on why this is happening?
What does the following give you:

SELECT Sum(DateDiff("s", [Time Start], [Time End])) AS TotalSeconds,
[TotalSeconds] \ 3600 AS Hours, ([TotalSeconds] Mod 3600 \ 60) AS
Minutes, TotalSeconds Mod 60 AS Seconds, Format(Hours, '00') & ':' &
Format(Minutes, '00') & ':' & Format(Seconds, '00') AS TotalTaskTime
FROM Task_LogTest;

Hint: You probably shouldn't store the [Total Task Time]. Note that
"hh:nn:ss" is more reliable than "hh:mm:ss" since it doesn't depend on
specialized behavior coded by Microsoft in order to make it work.

If this doesn't help be sure to post an example.

James A. Fortune
CD********@FortuneJames.com

Dec 15 '06 #2

P: n/a
Oops, sorry about the double post.

I've got some VBA code to automatically compute and store the total
task time, so we can have some idea on that. Originally it was a text
field and it worked fine, but then I needed to add up the times which
is why I changed it to Short Time. I'm in the process of updating
Office right now and I will try out the code that you posted. Thanks
for the help so far.

CD********@FortuneJames.com wrote:
Drew wrote:
I've got three columns i'm working on: Time Start (Date and Time), Time
End (Date and Time), Total Task Time (Short Time)

I've got Total Task Time working correctly. Here is my query:
SELECT Format(Sum([Total Task Time]),"hh:mm:ss")
FROM Task_LogTest;

However, I manually added it up (I've got 4 test rows) and the query is
returning a time that is off by 2 minutes exactly (my result + 2 min).
Any ideas on why this is happening?

What does the following give you:

SELECT Sum(DateDiff("s", [Time Start], [Time End])) AS TotalSeconds,
[TotalSeconds] \ 3600 AS Hours, ([TotalSeconds] Mod 3600 \ 60) AS
Minutes, TotalSeconds Mod 60 AS Seconds, Format(Hours, '00') & ':' &
Format(Minutes, '00') & ':' & Format(Seconds, '00') AS TotalTaskTime
FROM Task_LogTest;

Hint: You probably shouldn't store the [Total Task Time]. Note that
"hh:nn:ss" is more reliable than "hh:mm:ss" since it doesn't depend on
specialized behavior coded by Microsoft in order to make it work.

If this doesn't help be sure to post an example.

James A. Fortune
CD********@FortuneJames.com
Dec 15 '06 #3

P: n/a
On 15 Dec 2006 09:33:46 -0800, "Drew" <dr***********@gmail.comwrote:
>Oops, sorry about the double post.

I've got some VBA code to automatically compute and store the total
task time, so we can have some idea on that. Originally it was a text
field and it worked fine, but then I needed to add up the times which
is why I changed it to Short Time. I'm in the process of updating
Office right now and I will try out the code that you posted. Thanks
for the help so far.
A Date/Time field is used to store a specific point in time. (eg 16/12/2006
2:51PM)
It should NOT be used to store a time interval as you are attempting.

eg if you calculate that the interval between 2 times is 4 hours, and you store
this in a Date/Time field, the value stored actually refers to 31/12/1899
4:00AM.

What you are doing by using Short Time is trying to make this value "look like"
4hrs:0min but changing the format does nothing to the underlying value.

As James alluded to you, your best method is to calculate the time difference in
the minimum gradient that suits your needs (probably minutes) and then store the
total interval in either an Integer or Long field. Then create a function to
format the total as you want. (see James' SQL statement).
Wayne Gillespie
Gosford NSW Australia
Dec 16 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.