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([qryDateFilterTaskHours-Weekly].[NumberOfCompletions],0))
AS SumOfNumberOfCompletions,
Format(Sum(Nz([qryDateFilterTaskHours-Weekly].[HoursWorked],0)),"Short
Time") AS SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-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(ConvToMinutes(Nz(CStr([qryDateFilterTaskHours-Weekly].[HoursWorked]),"0:00")))
AS SumOfHoursWorked

Public Function ConvToMinutes(strTime 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(CStr([qryDateFilterTaskHours-Weekly].[HoursWorked],
Nz(Len(CStr([qryDateFilterTaskHours-Weekly].[HoursWorked]), 0) - 3, 0) * 60)
+ Nz(Right([qryDateFilterTaskHours-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:
=SumOfHoursWorked \ 60 & ":" & SumOfHoursWorked 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.googlegr oups.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([qryDateFilterTaskHours-Weekly].[NumberOfCompletions],0))
AS SumOfNumberOfCompletions,
Format(Sum(Nz([qryDateFilterTaskHours-Weekly].[HoursWorked],0)),"Short
Time") AS SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-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.googlegr oups.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([qryDateFilterTaskHours-Weekly].[NumberOfCompletions],0))
AS SumOfNumberOfCompletions,
sectodur(Sum([qryDateFilterTaskHours-Weekly].[HoursWorked]-40*60*60))
AS SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-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([qryDateFilterTaskHours-Weekly].[NumberOfCompletions],0))
AS SumOfNumberOfCompletions,
sectodur(Sum(([qryDateFilterTaskHours-Weekly].[HoursWorked]))) AS
SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-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.googlegr oups.com:

Sum(([qryDateFilterTaskHours-Weekly].[HoursWorked])))

sec2dur expects the duration in seconds. I assume you are feeding
it hours.
try multiplying by 3600
sec2dur(Sum(([qryDateFilterTaskHours-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.googlegr oups.com...

Bob,

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

SELECT Sum(Nz([qryDateFilterTaskHours-Weekly].[NumberOfCompletions],0))
AS SumOfNumberOfCompletions,
sectodur(Sum(([qryDateFilterTaskHours-Weekly].[HoursWorked]))) AS
SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-Weekly].ItemName;

OUTPUT: 0:00:02

It should be A LOT higher than that.

[Drum2001]

Ok, I changed the function to what you suggested.

With the following query, the output is the following:

------------------------------------------------------------------------------------------
SELECT Sum(Nz([qryDateFilterTaskHours-Weekly].[NumberOfCompletions],0))
AS SumOfNumberOfCompletions,
sectodur(Sum(([qryDateFilterTaskHours-Weekly].[HoursWorked]))*3600) AS
SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-Weekly].ItemName;

OUTPUT: 02:11:03
------------------------------------------------------------------------------------------

When the users input the time it is the following format [Hours] [Mins]

I then have a function that combines them to [HoursWorked]

That being said... 00:35 = 35 mins, 02:12 = 2 hours and 12 mins... so
the result of 02:11:03 doesn't work.

Any other thoughts? I really appreciate your assistance!

[Bob Quintal]

"Drum2001" <dr******@gmail.com> wrote in
news:11*********************@38g2000cwa.googlegrou ps.com:

Ok, I changed the function to what you suggested.

With the following query, the output is the following:

-------------------------------------------------------------- - --------------------------- SELECT
Sum(Nz([qryDateFilterTaskHours-Weekly]. [NumberOfCompletions],0) ) AS SumOfNumberOfCompletions,
sectodur(Sum(([qryDateFilterTaskHours-Weekly].[HoursWorked]))* 3 600) AS SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly]
ON ALLTasksFilter.ItemName =
[qryDateFilterTaskHours-Weekly].ItemName;

OUTPUT: 02:11:03
-------------------------------------------------------------- - ---------------------------

When the users input the time it is the following format
[Hours] [Mins]

I then have a function that combines them to [HoursWorked]

That being said... 00:35 = 35 mins, 02:12 = 2 hours and 12
mins... so the result of 02:11:03 doesn't work.

Any other thoughts? I really appreciate your assistance!

What is hoursworked?
Is it (hours*60+minutes)/60?
This returns hours and a fraction.

or does it do hours + minutes/100?

Either way, summing won't give you the correct answer.

Try changing the hoursWorked function to return the time in
seconds: Hours*3600+Minutes*60, take the multiplier (*3600) out
of the query) and see if that helps.


--
Bob Quintal

PA is y I've altered my email address.

[Drum2001]

What do you mean by the "HoursWorked Function"?

[Bob Quintal]

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

What do you mean by the "HoursWorked Function"?

You wrote When the users input the time it is the following format
[Hours] [Mins]

I then have a function that combines them to [HoursWorked]

--
Bob Quintal

PA is y I've altered my email address.

[Drum2001]

I see what you mean.

What I am actually doing is the following:

I have 2 boutnd text boxes: [HOURS] [MINS]
Then I have anothertext box (unbound) , [Text 28], which has the
control source of "=[Hours] & ":" & [Mins]"

Then I have a fourth text box (bound), [HOURS WORKED]. There is an ON
TIME EVENT (1000) on the form that executes the following:

Private Sub Form_Timer()
Me.HoursWorked = Me.Text28
End Sub

That is how I get everything into the form I want.

I know this may not be the most efficient way, but it is what I came up
with. If calculations can be done using the [HOURS] and [MINS] fields,
that would be fine.

[Bob Quintal]

"Drum2001" <dr******@gmail.com> wrote in
news:11**********************@g10g2000cwb.googlegr oups.com:

I see what you mean.

What I am actually doing is the following:

I have 2 boutnd text boxes: [HOURS] [MINS]
Then I have anothertext box (unbound) , [Text 28], which has
the control source of "=[Hours] & ":" & [Mins]"

Then I have a fourth text box (bound), [HOURS WORKED]. There
is an ON TIME EVENT (1000) on the form that executes the
following:

Private Sub Form_Timer()
Me.HoursWorked = Me.Text28
End Sub

That is how I get everything into the form I want.

I know this may not be the most efficient way, but it is what
I came up with. If calculations can be done using the [HOURS]
and [MINS] fields, that would be fine.
OK, let's fix up this query
----------------------------------------------------------------
sectodur(
Sum([qryDateFilterTaskHours-Weekly].[Hours]*3600
+[qryDateFilterTaskHours-Weekly].[Mins]*60
)
)
AS SumOfHoursWorked
----------------------------------------------------------------

--
Bob Quintal

PA is y I've altered my email address.

[Drum2001]

This seems to work when I have a very small sample, (Daily Totals).

However, when I tried to calculate monthly totals, it was still
incorrect.

With the query above, my results were
SumOfHoursWorked
338:33

When I calculate it out by hand, it should equal 52:25... 52 hours and
25 mins...

I don't know if its a matter of going over 24 hours again...

[Drum2001]

Wayne how could I adapt this work without combining "HoursWorked". I
do have the "time" captured in separate fields, [HOURS] & [MINS}

Thank you!

[Wayne Morgan]

If you have Hours and Mins in separate fields, where does the field
HoursWorked come from that you're trying to sum?

Format(Sum(Nz([qryDateFilterTaskHours-Weekly].[HoursWorked],0)),"ShortTime")
AS SumOfHoursWorked<<

I suspect you've taken the two fields and combined them somehow in
qryDateFilterTaskHours-Weekly. Is this correct? If so, what did you do to
combine them?

--
Wayne Morgan
MS Access MVP
"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com... Wayne how could I adapt this work without combining "HoursWorked". I
do have the "time" captured in separate fields, [HOURS] & [MINS}

Thank you!

[Drum2001]

What I am actually doing is the following:

I have 2 boutnd text boxes: [HOURS] [MINS]
Then I have anothertext box (unbound) , [Text 28], which has the
control source of "=[Hours] & ":" & [Mins]"

Then I have a fourth text box (bound), [HOURS WORKED]. There is an ON
TIME EVENT (1000) on the form that executes the following:

Private Sub Form_Timer()
Me.HoursWorked = Me.Text28
End Sub

That is how I get everything into the form I want.

The items are actually combined before the form is "submitted"

Does this help?

[Wayne Morgan]

There is no need to store HoursWorked, you have that information already in
the fields Hours and Mins. Just combine them for display purposes as
necessary, as you've done in Text28. This will also get rid of the
performance hit caused by running the timer. Also, even at once per second
(1000 milliseconds), you risk the form being "submitted" before the timer
runs. If you're going to do this, place the value in the HoursWorked textbox
in the form's BeforeUpdate event. This will always get the current
information available as the form is submitted. However, as I said, in this
case it is unnecessary and I would recommend getting rid of it.

Now, back to our original discussion. As previously mentioned an "elapsed
time" is not a "date/time" value. It may be formatted to look like one, but
it isn't. A date/time value is a specific date and/or specific time during
the day. That is why you're having problems going over 24 hours. There is
only 24 hours in a day. 3 hours worked, displayed as 03:00 is still the
value 3, not 3 AM.

To get the sum of the times, create a calculated field in the query to get
the sum then format it as desired.
Sum(Nz([Hours])) + (Sum(Nz([Mins])) \ 60) & ":" & (Sum(Nz([Mins])) Mod 60)

Going back to your original query:
SELECT Sum(Nz([qryDateFilterTaskHours-Weekly].[NumberOfCompletions],0))
AS SumOfNumberOfCompletions,

Sum(Nz([qryDateFilterTaskHours-Weekly].[Hours])) +
(Sum(Nz([qryDateFilterTaskHours-Weekly].[Mins])) \ 60) & ":" &
(Sum(Nz([qryDateFilterTaskHours-Weekly].[Mins])) Mod 60)
AS SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-Weekly].ItemName;

You'll need to make sure that the Hours & Mins fields are in the query
[qryDateFilterTaskHours-Weekly].

--
Wayne Morgan
MS Access MVP
"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

What I am actually doing is the following:

I have 2 boutnd text boxes: [HOURS] [MINS]
Then I have anothertext box (unbound) , [Text 28], which has the
control source of "=[Hours] & ":" & [Mins]"

Then I have a fourth text box (bound), [HOURS WORKED]. There is an ON
TIME EVENT (1000) on the form that executes the following:

Private Sub Form_Timer()
Me.HoursWorked = Me.Text28
End Sub

That is how I get everything into the form I want.

The items are actually combined before the form is "submitted"

Does this help?

[Drum2001]

Wayne,

Thank you! This works great!

The only question I have is with the formating. Right now for 2 hours
and 1min it is formating it as 2:1

Is there a way to make it say 2:01? If not, no biggie.

THANKS AGAIN!!!!

[Wayne Morgan]

Yes, that can be done.

SELECT Sum(Nz([qryDateFilterTaskHours-Weekly].[NumberOfCompletions],0))
AS SumOfNumberOfCompletions,
Sum(Nz([qryDateFilterTaskHours-Weekly].[Hours])) +
(Sum(Nz([qryDateFilterTaskHours-Weekly].[Mins])) \ 60) & ":" &
Format((Sum(Nz([qryDateFilterTaskHours-Weekly].[Mins])) Mod 60), "00")
AS SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-Weekly].ItemName;
--
Wayne Morgan
MS Access MVP
"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

Wayne,

Thank you! This works great!

The only question I have is with the formating. Right now for 2 hours
and 1min it is formating it as 2:1

Is there a way to make it say 2:01? If not, no biggie.

THANKS AGAIN!!!!