Adding times that exceed 24 hours?

I know this has been discussed quite a bit, but I can't seem to get my
head around the solution to this problem. I have a table with times
stored in 24 hour format and need to add those times on an access
report. Currently, I'm using sum and the column name; however, when
the time rolls over past 24 hours, a day is added (so 1:20:23:20 would
be 44 hours, 23 minutes, 20 seconds). Instead of 1:20:23:20, I need
44:23:20 to show on the report itself.

I understand Access isn't setup to be able to natively handle such a
task. I'm also not finding the difference between two times - I just
want to add all times that appear in a column for a specified query
and produce the result in hh:nn:ss format no matter how many hours it
may be.

What would be the easiest way to accomplish this?

0
Thiazi
4/12/2007 1:02:59 PM
access 16762 articles. 3 followers. Follow

6 Replies
820 Views

Similar Articles

[PageSpeed] 6

 http://www.mvps.org/access/datetime/date0009.htm

-- 
Joan Wild
Microsoft Access MVP
"Thiazi" <thiazi@gmail.com> wrote in message 
news:1176382979.504176.134110@q75g2000hsh.googlegroups.com...
>I know this has been discussed quite a bit, but I can't seem to get my
> head around the solution to this problem. I have a table with times
> stored in 24 hour format and need to add those times on an access
> report. Currently, I'm using sum and the column name; however, when
> the time rolls over past 24 hours, a day is added (so 1:20:23:20 would
> be 44 hours, 23 minutes, 20 seconds). Instead of 1:20:23:20, I need
> 44:23:20 to show on the report itself.
>
> I understand Access isn't setup to be able to natively handle such a
> task. I'm also not finding the difference between two times - I just
> want to add all times that appear in a column for a specified query
> and produce the result in hh:nn:ss format no matter how many hours it
> may be.
>
> What would be the easiest way to accomplish this?
> 


0
Joan
4/12/2007 1:34:30 PM
The ONLY way to accomplish this is to display the result as a string. 
The following function will take the sum of time values and convert to 
total hours and minutes:

Public Function TotalTime(dblTimeDif) As String

Dim intDays As Integer
Dim dblHrs As Double
Dim intHours As Integer
Dim dblMinutes As Double
Dim intMinutes As Integer
Dim intTime As Integer
Dim intFraction As Integer


intDays = Int(dblTimeDif)
dblHrs = (dblTimeDif - intDays) * 24
intHours = Int(dblHrs)
dblMinutes = dblHrs - intHours
intHours = intHours + (intDays * 24)
intMinutes = Int(dblMinutes * 60)


TotalTime = Str(intHours) & ":" & Trim(Str(intMinutes))

End Function

HTH
Scott<>
Microsoft Access MVP 2007


Thiazi wrote:
> I know this has been discussed quite a bit, but I can't seem to get my
> head around the solution to this problem. I have a table with times
> stored in 24 hour format and need to add those times on an access
> report. Currently, I'm using sum and the column name; however, when
> the time rolls over past 24 hours, a day is added (so 1:20:23:20 would
> be 44 hours, 23 minutes, 20 seconds). Instead of 1:20:23:20, I need
> 44:23:20 to show on the report itself.
> 
> I understand Access isn't setup to be able to natively handle such a
> task. I'm also not finding the difference between two times - I just
> want to add all times that appear in a column for a specified query
> and produce the result in hh:nn:ss format no matter how many hours it
> may be.
> 
> What would be the easiest way to accomplish this?
> 


-- 
HTH
Scott<>
Microsoft Access MVP 2007
0
Scottgem
4/12/2007 2:35:37 PM
Where would I add this function (I assume as a module) and reference
it on the reports? Right now I just have a textbox with control set to
=sum([field])



Scottgem (MVP) wrote:
> The ONLY way to accomplish this is to display the result as a string.
> The following function will take the sum of time values and convert to
> total hours and minutes:
>
> Public Function TotalTime(dblTimeDif) As String
>
> Dim intDays As Integer
> Dim dblHrs As Double
> Dim intHours As Integer
> Dim dblMinutes As Double
> Dim intMinutes As Integer
> Dim intTime As Integer
> Dim intFraction As Integer
>
>
> intDays = Int(dblTimeDif)
> dblHrs = (dblTimeDif - intDays) * 24
> intHours = Int(dblHrs)
> dblMinutes = dblHrs - intHours
> intHours = intHours + (intDays * 24)
> intMinutes = Int(dblMinutes * 60)
>
>
> TotalTime = Str(intHours) & ":" & Trim(Str(intMinutes))
>
> End Function
>
> HTH
> Scott<>
> Microsoft Access MVP 2007
>
>
> Thiazi wrote:
> > I know this has been discussed quite a bit, but I can't seem to get my
> > head around the solution to this problem. I have a table with times
> > stored in 24 hour format and need to add those times on an access
> > report. Currently, I'm using sum and the column name; however, when
> > the time rolls over past 24 hours, a day is added (so 1:20:23:20 would
> > be 44 hours, 23 minutes, 20 seconds). Instead of 1:20:23:20, I need
> > 44:23:20 to show on the report itself.
> >
> > I understand Access isn't setup to be able to natively handle such a
> > task. I'm also not finding the difference between two times - I just
> > want to add all times that appear in a column for a specified query
> > and produce the result in hh:nn:ss format no matter how many hours it
> > may be.
> >
> > What would be the easiest way to accomplish this?
> >
>
>
> --
> HTH
> Scott<>
> Microsoft Access MVP 2007

0
Thiazi
4/12/2007 3:44:41 PM
Where would I add this function (I assume in a module) and how would I
call it from the report to convert the result? Right now I have a
textbox in the report using =sum([field])

Scottgem (MVP) wrote:
> The ONLY way to accomplish this is to display the result as a string.
> The following function will take the sum of time values and convert to
> total hours and minutes:
>
> Public Function TotalTime(dblTimeDif) As String
>
> Dim intDays As Integer
> Dim dblHrs As Double
> Dim intHours As Integer
> Dim dblMinutes As Double
> Dim intMinutes As Integer
> Dim intTime As Integer
> Dim intFraction As Integer
>
>
> intDays = Int(dblTimeDif)
> dblHrs = (dblTimeDif - intDays) * 24
> intHours = Int(dblHrs)
> dblMinutes = dblHrs - intHours
> intHours = intHours + (intDays * 24)
> intMinutes = Int(dblMinutes * 60)
>
>
> TotalTime = Str(intHours) & ":" & Trim(Str(intMinutes))
>
> End Function
>
> HTH
> Scott<>
> Microsoft Access MVP 2007
>
>
> Thiazi wrote:
> > I know this has been discussed quite a bit, but I can't seem to get my
> > head around the solution to this problem. I have a table with times
> > stored in 24 hour format and need to add those times on an access
> > report. Currently, I'm using sum and the column name; however, when
> > the time rolls over past 24 hours, a day is added (so 1:20:23:20 would
> > be 44 hours, 23 minutes, 20 seconds). Instead of 1:20:23:20, I need
> > 44:23:20 to show on the report itself.
> >
> > I understand Access isn't setup to be able to natively handle such a
> > task. I'm also not finding the difference between two times - I just
> > want to add all times that appear in a column for a specified query
> > and produce the result in hh:nn:ss format no matter how many hours it
> > may be.
> >
> > What would be the easiest way to accomplish this?
> >
>
>
> --
> HTH
> Scott<>
> Microsoft Access MVP 2007

0
Thiazi
4/12/2007 3:58:38 PM
"Thiazi" <thiazi@gmail.com> wrote in message 
news:1176382979.504176.134110@q75g2000hsh.googlegroups.com...
>I know this has been discussed quite a bit, but I can't seem to get my
> head around the solution to this problem. I have a table with times
> stored in 24 hour format and need to add those times on an access
> report. Currently, I'm using sum and the column name; however, when
> the time rolls over past 24 hours, a day is added (so 1:20:23:20 would
> be 44 hours, 23 minutes, 20 seconds). Instead of 1:20:23:20, I need
> 44:23:20 to show on the report itself.
>
> I understand Access isn't setup to be able to natively handle such a
> task. I'm also not finding the difference between two times - I just
> want to add all times that appear in a column for a specified query
> and produce the result in hh:nn:ss format no matter how many hours it
> may be.
>
> What would be the easiest way to accomplish this?
> 

0
i_takeuti
4/12/2007 9:48:49 PM
I've successfully implemented this. However - can you only call it
once in a report? If I attempt to call it on more than one field at a
time, I get #Errors and an automation error 440.

On Apr 12, 5:48 pm, "i_takeuti" <i_take...@mx9.kct. ne.jp> wrote:
> "Thiazi" <thi...@gmail.com> wrote in message
>
> news:1176382979.504176.134110@q75g2000hsh.googlegroups.com...
>
>
>
> >I know this has been discussed quite a bit, but I can't seem to get my
> > head around the solution to this problem. I have a table with times
> > stored in 24 hour format and need to add those times on an access
> > report. Currently, I'm using sum and the column name; however, when
> > the time rolls over past 24 hours, a day is added (so 1:20:23:20 would
> > be 44 hours, 23 minutes, 20 seconds). Instead of 1:20:23:20, I need
> > 44:23:20 to show on the report itself.
>
> > I understand Access isn't setup to be able to natively handle such a
> > task. I'm also not finding the difference between two times - I just
> > want to add all times that appear in a column for a specified query
> > and produce the result in hh:nn:ss format no matter how many hours it
> > may be.
>
> > What would be the easiest way to accomplish this?- Hide quoted text -
>
> - Show quoted text -


0
Thiazi
4/13/2007 1:24:51 AM
Reply:

Similar Artilces:

Unable to update free/busy time
Hi there, This morning I installed the Exchange 2000 post-sp3 rollup on our small business server. Since then users have been getting messages from Outlook saying that it can't update free/busy time on the server. Prior to installing the rollup we did not get the errors. I had a look at the event log on the server and it is filling up with EventID: 1546 errors, the description is: Workflow event sink error [event: OnSyncSave, URL: <file://./backofficestorage/test.com.au/Public Folders/NON_IPM_SUBTREE/SCHEDULE+ FREE BUSY/EX:_xF8FF_o=DSPACE_xF8FF_ou=first administrative group/USER-_xF8...

Run-time error '5'
Hello, I'm getting a weird error on one of our computers. As soon as I open Excel and click anywhere on the sheet, I get an error: Run-time error '5' Invalid procedure or call argument This is just a blank document, with no macros or VBA code. Why am I getting it? I would appreciate your help. Thank you, -- Peter Afonin Hi try removing all add-ins -- Regards Frank Kabel Frankfurt, Germany Peter Afonin wrote: > Hello, > > I'm getting a weird error on one of our computers. As soon as I open > Excel and click anywhere on the sheet, I get an error: > ...

Adding stages to a Sales Process
Hi, I want to add two new stages to a current sales process. Has anyone done this before? Any issues arise with previous opportunties, reporting, etc? Thanks, Sinead. -- Sinead O''''Shaughnessy previous opportunities will not see the new stages ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "Sinead" <Sinead@discussions.microsoft.com> wrote in message news:8EC75D1E-06C5-4A6E-813D-1B5816DE87B6@microsoft.com... > Hi, > I want to add two new stages to a current sales process. Has anyone done > this bef...

How to have one year value added to another cell
I have a cell where I date foramtted fill in the date. How to have on another cell the date + one year automatically filled? So, A1 02-04-06 auto: B1 02-04-07 Bart Excel 2003 =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Bob Umlas Excel MVP "AA Arens" <bartvandongen@gmail.com> wrote in message news:1164983094.802613.212520@l12g2000cwl.googlegroups.com... > I have a cell where I date foramtted fill in the date. > How to have on another cell the date + one year automatically filled? > > So, > > A1 02-04-06 > > auto: > B1 02-04-07 > > > Bart >...

Adding sums in rows and columns and colouring cells with conditions
Hi all, I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another...

Cannot start Microsoft Office Outlook 2007 11-24-09
I can no longer access Microsoft Outlook since having ran out of memory on the C:\ After deleting some and letting the disk clean up porgram delete some other files I now receive this error message. "Cannot start Microsoft Office Outlook. Cannot open the Outlook Window" Can anyone help me please - Thank you By memory I guess you mean hard disk space. If you have less than 15% of free space you will run into other win problems - time for a new larger disk? Try Run outlook.exe /resetnavepane (note space between exe /) "Rita" <Rita@discussions.micro...

Total Minutes Worked Within IVR Timing
Hi, I need to figure out how many total minutes employee worked during the IVR Timing. Each employee works different shift and it can run overnight into next morning where there may be IVR coverage before employee logs out. IVR Timing is same for Saturday/Sunday and same from Monday to Friday. IVR Timing Table; data type is Date/Time but only Time is available because date should be same as the schedule date DayIs IVRStart IVRStop Sunday 10:00:00 AM 6:00:00 PM Monday 9:00:00 AM 9:00:00 PM Tuesday 9:00:00 AM 9:00:00 PM Wednesday 9:00:00 AM 9:00:00 PM Thursday 9:00:00 AM ...

Adding text and date problem
I have text in column 1, a date in column 2 and in column 3 I wish to show the text followed by the date. I have tried both Concatenate and & but the the result is text followed by the underlying number representing the date. I cannot seem to format this column to show the date as dd/mm/yy. Any suggestions? Regards ="Today is: "&TEXT(A1,"mm/dd/yyyy") -- Kind regards, Niek Otten Microsoft MVP - Excel "newman" <aa111@despammed.com> wrote in message news:464C1646.AC6A42E0@despammed.com... |I have text in column 1, a date in column 2 and in column...

adding values of cells
I am trying to use a formula or function that will look at a cells say column A check for a condition, if true, then add the value of another cell that is on the same row to another cell. Yes I know that may be difficult to understand so let me give you an example. A B C D 1 2 dvd 25.23 3 cd 12.25 4 dvd 25.23 5 cd 12.25 6 bat 19.25 ok so what i want to do is look at column A, and look for any cells that have dvd and add the corresponding value from column D to cell B1. So in this case we can see the cell A2 and cell A4 is dvd so I want to add cells D2 and D4 and that...

Adding and subtracting from a column.
I have a stock list with a "quantity" column. What I would like to do is add the new stock to the existing quantity column or subtract requisitioned items from the quantity column. I guess it is a "self totaling" column I need to make. As a separate exercise I would like to make a new column for orders. Insert a quantity on that column for any of the items ( rows) I want to order and then print it out... BUT I only want to print the rows that have quantities in that column ( i.e. the actual items I am ordering ). This column is not linked to the above column. Could an...

Adding items (hours) into invoices
Hello, In my M07 Home and Business, I get an error while entering an invoice as soon as I add hours and press enter. The program says that there's an error: "This operation can not be performed" When I select more help, I get a help page that say:Details Id: obres:34 Bron: 16.0 When I search on this I see that it is an error that's related to homepage settings, but that does not work for me. Any suggestions? Thanks, Eric ...

email account will not show up when added: says deleted
my main email account will not show up in the "accounts" section of Outlook 2003 even though I have added it and the "test" button shows it is set up correctly. It is listed in the accounts pane of add/change email accounts but when I try to set it as the default account I get a message it is not found and probably was deleted. I have deleted and re added it several times with the same result. Any solution ? Alec McNaughton <anonymous@discussions.microsoft.com> wrote: > my main email account will not show up in the "accounts" > section of Outl...

total of a range of times
This should be simple for some of you, but not for me. I am a runner, I have 4 ind. cells w/my split times. How do I get a total for the tallied times. I'm far from being experienced but am trying to learn..any and all help will be much appreciated. Thanks, Roger Roger To help you in your quest to "try to learn" I will direct you to Chip Pearson's site so's you can learn just about all you'll ever need for Time Calculations. http://www.cpearson.com/excel/datetime.htm#AddingTimes Gord Dibben Excel MVP On Sat, 13 Nov 2004 15:19:02 -0800, "Roger WG" &...

Autoformat dates and times
Hi, I have a workbook with two columns. In the formula bar of one of the cells i have 2005040101700 (YearMonthDateHoursMinutes), but what shows in the cell is 2.00504E+11 When I try to format the cell as yyyymmddhhmm I get ########### and when I put the cursor over the cell with ############### it says that negative dates and times are shown this way. How can I make this work? Thanks. Allison Allison, I'm hoping that your actual value is different: 2005040101700 doesn't make sense: year 2005 month 04 day 01 hour 01 minutes 70 seconds 0 Perhaps it's really 200504010...

Adding 2nd Y-axis to Excel2003 chart
I am making a simple line chart with two types of column data. I want to represent them with separate Y-axes. However, when I select the two columns of data and go through the Chart wizard the secondary Y-axis option is inaccessible. Excel 2003 help is no help as it tells me to go to the Format Menu, click Selected Data Series then click the Axis tab, but there is no such option under my format menu. How do I select the secondary Y-axis option? Hi, The chart wizard does not give you the option to place the selected data on 2 axes. You will have to do it once the chart is cre...

Access Denied error while edit some of the GPOs in Windows 2003 AD
Hi We are unable to edit some of the GPOs (Default Domain Policy, etc) and getting Access Denied error. We checked the permission of SYSVOL folder and found Administrators (Domain), System and Authenticated Users have full control share permissions. Full access has been provided to Administrators, creator owner & System and read & execute permission has been provided to Authenticated users in Security tab. Can anyone help me to resolve the issue and also any doc is available to check the correct permissions with SYSVOL. Thanks in advance for help Regards Lal -...

URGENT!! Email was received hours delayed!!!!
I have exchange server 5.5 SP 4 on NT 4.0 SP 6.0 I send several mails with recipients from our local domains and outside our domain (actually located in other countries) the local recipients received the mail fast but when we check the recipients located in other countries... they havent received the mails yet.. and it took almost 16 hours before they send confirmation that they received emails. I have checked exchange and bound that entries on outbound queues are few while inbound awaiting delivery contains so many entries what could this mean???? please help "baby" &...

Total working hours problem if it start from 30 at night and ends on the 31 morning
Dear all, i am using this to get the total wroking hours per day TOTAL WORKING HOURS PER DAY: DateDiff("n",[TIME IN],[TIME OUT]+DateDiff ("n",[TIME IN 2],[TIME OUT 2]))/60 It is working perfectly until i have this Employee Name Day Date Time Clock In/OUT Tia Kareem Fri 10/30/2009 5:59pm IN Tia Kareem Sat 10/31/2009 3:27am OUT IF i will use the same expression i will be getting total working hours per day : -14.53 Employee Name Date IN ...

Adding a password to a reminder?
This is a family PC which several of us use. Outlook 2002, XP ProSP2. There have been a couple of occasions when one of us have set a reminder in Calendar, but it has popped up when another user is at the machine. Teenagers being what they are, they don't appreciate the importance to someone else, and its been 'dismissed'. The Mum misses a deadline cos no-one reminded her.... I realise the answer to this lies in our helping one another out - and I'm creasonably confident that will happen - but I wonder if as a failsafe I can prevent an item being dismissed in some way...

Time total
Hello All, I need some help with creating a query to sum a total time of a field. I have a table called client services and have a query which pulls: date of service, total time, and provider name. I would like to be able to create a query which will sum the total time field. I have look at other posts on here but am still confused. Also not sure what time set to use: minutes or hour/minute format. Thanks for your help, -Mark Mark, I assume that you want to total time across clients, do you want to do it for a specific period, (June 07) or just get the total time? To get the total ...

rpc over http long logon time
How long should it take after Outlook starts before the prompt appears and asks for the password? Then afer that, how long before Outlook connects and starts downloading mail? Ours takes from 30 seconds to a minute for both steps. User are very confused why they start Outlook and have to enter their password a minute later, and still can't use Outlook for another minute. Thanks, Neal ...

date and time calculations in Excel 2003
Example: H6=01/14/2010 16:45 A6=12/31/2009 12:15 I want to get the difference between January 14th 2010 at 16:45 and December 31, 2009 at 12:15. Ideally, it would be days, hours and minutes. Like this==> ddd:hh:mm The formula is: =h6-a6 Use a custom format of: dd:hh:mm Regards, Fred "CJ" <CJ@discussions.microsoft.com> wrote in message news:1D38B37F-ED29-499E-A05E-F1682FF9F153@microsoft.com... > Example: > H6=01/14/2010 16:45 > A6=12/31/2009 12:15 > > I want to get the difference between January 14th 2010 at 16:45 and > D...

at times i have to reboot to start Outlook 2003, any help?
it would be appreciated Sounds like you have a 3rd party application or addin that isn't letting Outlook close properly. Try bringing up the task list (start > run > tasklist.exe > ok button) to see if Outlook is still running when it won't start. (check the processes tab) "skimmi" <skimmi@discussions.microsoft.com> wrote in message news:42FAFCC7-55BC-45DB-B8D7-674FF9638B14@microsoft.com... > it would be appreciated Not sure if this helped or not? The problem did happen again and tried what you suggested but the screen only flashed up momentari...

adding second y axix
I have a chart that looks like this Dates (should be in the Y axis) number ( 1,2 ,3 ect) in the x axis cost (should be the second y axis) using Excel 2002 - Scatter Chart - can this be done -- Message posted from http://www.ExcelForum.com HELP!!! Any one?? -- Message posted from http://www.ExcelForum.com Put Numbers in col A Dates in B Cost in C Select A1:C20 (or what ever); make chart Right click the Cost line in chart, open Format Data Series and look for Secondary Axis best wishes Bernard "mmick >" <<mmick.1cog6h@excelforum-nospam.com> wrote in message news...

single user email gets lost or arrives hours late
I have a user that is using office xp. we run excg5.5. he can send out an email to a user and sometimes the user gets it and sometimes he dosen't. we don't get bounce back message. i am trying to figure out what the problem is. The users address is saved in the users contacts list. ...