Adding Time over 24 hours

[Drum2001]

I have a table that tracks employee times. I have a column
[HOURSWORKED] (Date/Time).
Users, through a form, enter how long it takes them to complete a task.
For example, 03:45 = 3 hours and 45 mins.

I am running the following query:

SELECT Sum(Nz([qryDateFilterTa skHours-Weekly].[NumberOfComplet ions],0))
AS SumOfNumberOfCo mpletions,
Format(Sum(Nz([qryDateFilterTa skHours-Weekly].[HoursWorked],0)),"Short
Time") AS SumOfHoursWorke d
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTa skHours-Weekly] ON
ALLTasksFilter. ItemName = [qryDateFilterTa skHours-Weekly].ItemName;

This query is designed to total all the times within a specific time
period. However, when the time goes over 24 hours, it does not give
the correct time anymore.

Any suggestions on what I may do to acomplish this?

Thank you for your assistance!

[jleckrone]

It looks time you are adding actual clock times not total times.
Obviously if you have 6:00 and add 7:00 to it you are going to get
13:00 (add 7 hours to 6 AM and you get 13:00 or 1 PM). I think what
you need is something like this:
Sum(Hour([TimeWorked]))+(Sum(Minute([TimeWorked]))\60) for your hours
worked and Sum(Minute([TimeWorked])) Mod 60 for minutes worked. Hope
that helps!

[Wayne Morgan]

The problem is that 03:45 is 3:45 AM, not 3 hours and 45 minutes. You are
dealing with an elapsed time, which is not an actual Time value. A Time
value is a point in time, such as 24 June 2005 at 3:45 AM.

To handle this, you need to convert the entry into minutes (convert
everything to the smallest unit you're using to simplify the addition), add
up the minutes, then format it as desired. If you want to use the hh:nn
format, that's fine. Just remember that it is only that, a format, not a
value.

This can be done using a user defined function or possibly in the query
itself.

Example (user defined function):
Sum(ConvToMinut es(Nz(CStr([qryDateFilterTa skHours-Weekly].[HoursWorked]),"0:00")))
AS SumOfHoursWorke d

Public Function ConvToMinutes(s trTime As String) As Long
Dim strArray() As String
ReDim strArray(2)
strArray() = Split(strInput, ":")
ConvToMinutes = CInt(strArray(0 )) * 60 + CInt(strArray(1 ))
End Function

Another possibility that you may be able to do in the query without the user
defined function (untested):
Sum((Nz(Left(CS tr([qryDateFilterTa skHours-Weekly].[HoursWorked],
Nz(Len(CStr([qryDateFilterTa skHours-Weekly].[HoursWorked]), 0) - 3, 0) * 60)
+ Nz(Right([qryDateFilterTa skHours-Weekly].[HoursWorked], 2), 0))

Then format the result back to hours:minutes, you may want to do this in the
form or report that displays the answer instead of in the query.
Possible Control Source:
=SumOfHoursWork ed \ 60 & ":" & SumOfHoursWorke d Mod 60

Other options include making the field two fields, one for hours and one for
minutes. This would save splitting it into hours and minutes to do the
calculation (it would already be split), but the rest would be the same. You
would still multiply the hours by 60 then add them to the minutes.

--
Wayne Morgan
MS Access MVP
"Drum2001" <dr******@gmail .com> wrote in message
news:11******** **************@ j55g2000cwa.goo glegroups.com.. .

I have a table that tracks employee times. I have a column
[HOURSWORKED] (Date/Time).
Users, through a form, enter how long it takes them to complete a task.
For example, 03:45 = 3 hours and 45 mins.

I am running the following query:

SELECT Sum(Nz([qryDateFilterTa skHours-Weekly].[NumberOfComplet ions],0))
AS SumOfNumberOfCo mpletions,
Format(Sum(Nz([qryDateFilterTa skHours-Weekly].[HoursWorked],0)),"Short
Time") AS SumOfHoursWorke d
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTa skHours-Weekly] ON
ALLTasksFilter. ItemName = [qryDateFilterTa skHours-Weekly].ItemName;

This query is designed to total all the times within a specific time
period. However, when the time goes over 24 hours, it does not give
the correct time anymore.

Any suggestions on what I may do to acomplish this?

Thank you for your assistance!

[Drum2001]

One thing that may help is I have the information already captured as 2
separate columns....

[Hours] [Mins] (Both Int)

Would it be easier to add these up, but still format as time?

What would I do exacty?

[Jeff L]

Sum(Hours)+(Sum (Mins)\60) for your hours
worked and Sum(Minutes) Mod 60 for minutes worked.
To display in time format (Hours:Mins)
Sum(Hours)+(Sum (Mins)\60) & ":" & Sum(Minutes) Mod 60

[Bob Quintal]

"Drum2001" <dr******@gmail .com> wrote in
news:11******** **************@ i40g2000cwc.goo glegroups.com:

One thing that may help is I have the information already
captured as 2 separate columns....

[Hours] [Mins] (Both Int)

Would it be easier to add these up, but still format as time?

What would I do exacty?

time in database lingo is different from duration.
Time is only the specific instance when some event occurs.
duration is the difference between two times.
Instead of formatting as time you need to create a custom format,
or google the archives of this group for a function called sec2dur
which builds a format around a duration in seconds, You could
modify this easily to do the duration in minutes.

--
Bob Quintal

PA is y I've altered my email address.

[Drum2001]

Bob,

Thanks for your assistance again. I used the function. When I run the
following query, I get a really strange number...

SELECT Sum(Nz([qryDateFilterTa skHours-Weekly].[NumberOfComplet ions],0))
AS SumOfNumberOfCo mpletions,
sectodur(Sum([qryDateFilterTa skHours-Weekly].[HoursWorked]-40*60*60))
AS SumOfHoursWorke d
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTa skHours-Weekly] ON
ALLTasksFilter. ItemName = [qryDateFilterTa skHours-Weekly].ItemName;

OUTPUT: -7,040:00:02

Any suggestions?

[Drum2001]

Bob,

Disregard the last post. I sent the wrong information.
I am running the following query:

SELECT Sum(Nz([qryDateFilterTa skHours-Weekly].[NumberOfComplet ions],0))
AS SumOfNumberOfCo mpletions,
sectodur(Sum(([qryDateFilterTa skHours-Weekly].[HoursWorked]))) AS
SumOfHoursWorke d
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTa skHours-Weekly] ON
ALLTasksFilter. ItemName = [qryDateFilterTa skHours-Weekly].ItemName;

OUTPUT: 0:00:02

It should be A LOT higher than that.

[Bob Quintal]

"Drum2001" <dr******@gmail .com> wrote in
news:11******** **************@ i39g2000cwa.goo glegroups.com:

Sum(([qryDateFilterTa skHours-Weekly].[HoursWorked])))

sec2dur expects the duration in seconds. I assume you are feeding
it hours.
try multiplying by 3600
sec2dur(Sum(([qryDateFilterTa skHours-Weekly].[HoursWorked]))*3600)
--
Bob Quintal

PA is y I've altered my email address.

[Wayne Morgan]

I don't see in your query where you're adding the hours and minutes
separately or changing to hours to minutes so that you can add them all at
once. You must add (Sum) just one type of unit at a time, either by
converting everything to the same type of unit (i.e. minutes or hours) or by
adding them separately.

--
Wayne Morgan
MS Access MVP
"Drum2001" <dr******@gmail .com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. .

Bob,

Disregard the last post. I sent the wrong information.
I am running the following query:

SELECT Sum(Nz([qryDateFilterTa skHours-Weekly].[NumberOfComplet ions],0))
AS SumOfNumberOfCo mpletions,
sectodur(Sum(([qryDateFilterTa skHours-Weekly].[HoursWorked]))) AS
SumOfHoursWorke d
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTa skHours-Weekly] ON
ALLTasksFilter. ItemName = [qryDateFilterTa skHours-Weekly].ItemName;

OUTPUT: 0:00:02

It should be A LOT higher than that.