473,585 Members | 2,501 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Adding Time over 24 hours

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!

May 19 '06 #1
22 14957
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!

May 19 '06 #2
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!

May 19 '06 #3
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?

May 19 '06 #4
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

May 19 '06 #5
"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.
May 19 '06 #6
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?

May 22 '06 #7
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.

May 22 '06 #8
"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.
May 22 '06 #9
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.

May 22 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2785
by: Paul | last post by:
Hi I have a table that currently has 466 columns and about 700,000 records. Adding a new DEFAULT column to this table takes a long time. It it a lot faster to recreate the table with the new columns and then copy all of the data across. As far as I am aware when you add a DEFAULT column the following happens:
5
4735
by: Nicolas Mejia | last post by:
Hello: I´m not a professional programer in Access. I´m working in a proyect that will permit me add my flight time, but I have been having problems with the time adding. Please can some one gve me a clue to how i can add time that exceeds 24 hours (Eg. 500:47) Thans a lot
0
1343
by: chrisbenoit06 | last post by:
Howdy...anyone able to assist me in adding the function to calculate the gross pay, and also the calculation and printing of the totals of hours and gross pay....thoughts? /* Purpose: This program calculates the gross pay for employees. * It allows the user to enter employee numbers, hours worked, * and rates of pay,...
27
2846
by: Rio Liaden | last post by:
Hi all! My database allows all employees to enter their time and get a report at any time. The first screen has a calendar which is locked to the last day of the pay period and that date is the only one currently in place. The user selects his/her name from a dropdown, clicks the calendar, then "Next" to get to the time input screen. There...
3
5568
by: Nigel Heald | last post by:
Hi Folks, We have a form that records flight times in hours and minutes, for example a 1 hour 15 minute flight is recorded as 1:15 Does anyone know how to get Access 2003 to calculate a total figure for a number of flight times recorded in a datasheet form? and is there a way to display flight times greater than 24 hours i.e 26:20 for...
1
2950
by: Nigel Heald | last post by:
Hi Folks, We have a form that records flight times in hours and minutes, for example a 1 hour 15 minute flight is recorded as 1:15 Does anyone know how to get Access 2003 to calculate a total figure for a number of flight times recorded in a datasheet form? and is there a way to display flight times greater than 24 hours i.e 26:20 for...
5
27154
by: lofty | last post by:
Hi, I'm building a time report system for work and I want to show the total hours and minutes a project have taken but can't get it to work ... I have a database (MySQL) in one table I have the date in one colum and the time in one colum. The date i formated like this YYYY-MM-DD and set to date. And the TIME is like this HH:mm:ss. My...
8
2961
Dököll
by: Dököll | last post by:
All it is, is a simple program that asks to enter hour and minute and makes the clock tick, after ticking, when clock is viewed, it should read a minute ahead. Very proud of the results. The only issue is getting PM to fire when time ticks beyond 24 hours. Just a matter of time, but do tell me what you see happening, I am sure it is pretty...
1
3586
by: George | last post by:
Hello........I have a problem adding up record entries with more than 24 hours when the fields are formatted as hour/minute (h:m). If the actual summed total of all records is greater than 24 hours, then the hour part of the answer is truncated to a number less than 24 based on however many multiples of 24 were part of the summed answer...
0
7836
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7950
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6606
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5710
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3835
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2343
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1447
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1175
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.