Calculating time greater than 24 hours

[billyw1953]

I have looked through the threads and could not find anything on date and time calculations, if I missed something please forgive my repeated question.

I have a table (datetimeloctable) which calls for both date and time field entries ie;

datetimelocbegins and datetimelocends

I am keeping track of locations that are monitored and have been trying to total the sum in a report in hours and minutes. Basically I have figured out that access does not seem to total time correctly after 24 hours have elapsed. Is there a fairly simple work around to this problem? I have seen it addressed using a module but wondered if there might be a simpler way? Any help is truly appreciated and any more information needed will gladly be submitted.

 

[Pat Hartman]

Use DateDiff() to calculate the elapsed time in seconds. sum this value in a totals query. Format the result using the function from this sample db:
Sample Date Functions

 

[billyw1953]

Thanks for the help, used the datediff(s) to convert the time to seconds in a query and it totaled up just fine. Checked the math and 15 min converted correctly to 900 sec. Now the proper formating has me stumped.

I downloaded your sample data base and looked around but probably don't understand enough to go any further, I will read the dang book some more.

I noticed in your sample database the GetTimeCardTotal function in your modules. This I read in the microsoft knowledge base and it is what I believe applies to my case as I see it and is what I was trying to avoid. I have never used a module so if this is what you meant, I appreciate your time and I will try to figure it out.

 

[Jon K]

.... access does not seem to total time correctly after 24 hours have elapsed.

Access does total time correctly after 24 hours have elapsed.


Time is internally stored as a double precision number, with the integer portion representing the day and the decimal portion representing the time. Time elapsed, that is [DateTimeLocEnds]-[DateTimeLocBegins], remains a double precision number, so all we need is format it properly.


To get elapsed time, we can use:-
Int([DateTimeLocEnds]-[DateTimeLocBegins]) & " day " & Format([DateTimeLocEnds]-[DateTimeLocBegins], "h \h\r n \m\i\n s \s\e\c")

To get total elapsed time, we can use:-
Int(Sum([DateTimeLocEnds]-[DateTimeLocBegins])) & " day " & Format(Sum([DateTimeLocEnds]-[DateTimeLocBegins]), "h \h\r n \m\i\n s \s\e\c")

These expressions will show the elapsed time in ? day ? hr ? min ? sec.

I have attached a database with two queries and a report for demonstration.


Note   A back slash in the format tells Access the following character is a literal string, so \h\r will return the text string hr. Access has the habit of replacing the back slashes with "", e.g. \h\r may become "" hr "" when a query (created or edited in Design View) or a report is saved.

You can simply type "h hr n min s sec" and then add the back slashes or surround each of the hr min sec by two double quotes.

 

[billyw1953]

Thank you very much Jon, this code works perfectly. I truly appreciate the time you took to help me out with this.

 

[billyw1953]

Jon K would you look at the attached sample database you created. Your report with very few exceptions mirrors the one in my original database. When I utilized your functions, the report was completely accurate using days, hours, minutes, and seconds. However, I really need to see only hours and minutes as the totals, so I butchered your report a little and I'm back to not getting accurate times in the totals and grand total fields. In this report, the first total should be 39:29 in hours and minutes, the second total should be 33:07 and the grand total should read 72:36
Except for the time problem I'm having, the database is working perfectly and this is driving me crazy. Sorry I'm so damn stupid but this is my first database design and I am truly trying, I'm just not anywhere near you guys level and I feel really dumb.

 

[Jon K]

To exclude the seconds, you can deduct the seconds from the difference of [DateTimeLocEnds]-[DateTimeLocBegins], that is

[DateTimeLocEnds]-[DateTimeLocBegins]-CDate("0:0:" & DatePart("s",[DateTimeLocEnds]-[DateTimeLocBegins]))


See the report "Report in hours minutes".

 

[Pat Hartman]

I didn't look at the example so I'm not sure where this code will end up but be careful not to truncate the remaining seconds too early or you'll end up with huge rounding errors. You don't want to get rid of the remaining seconds until after the elapsed time is summed. That way your maximum error is 59 seconds. If you get rid of seconds at each record, your rounding error exposure is 59 seconds per row.

 

[billyw1953]

Thanks Pat, so far the code works perfectly. I think I understand what you are saying, I will probably make a copy of my database and enter some dummy times in there so I can watch for margins of error as it grows. I appreciate your help but embarrassingly enough for me I just could not quite grasp all you were telling me, I'm just not that adavanced. I needed someone to walk me through this step by step and I am so thankful there are people like you and Jon willing to help dummies like me.

BWard

 

[andrewsg]

Hello,

I'm quite new to Access and have created a nice little database logging Lawyers hours and work and creating Invoices and reports from it.

One of the reports required the total cost of the lawyers work and included the individual job hours.

I was asked to have a total hours at the bottom (sum of hours) and I had the problem of it restarting after each 24 hour period. Lawyers work more than 24 hours in a day almost lol.

I was looking for a simple answer and actually managed to work it out for myself. If you set the format to a standard number format, or a single or a double then you get a result that is just a fraction of the correct answer, in fact it is an actual fraction 1/24th.

Answer: set the format to standard to get a decimal result.

set the calculation as a sum and multiply the result by 24 hrs: e.g. =sum([hoursWorked])*24

The format will be decimal i.e. 43 hours and 30 minutes will be displayed as 43.5, but as long as that is fine for you then your sorted.

Hope this helps

Andrew