Time calculation

rdfernandez · March 23rd, 2005, 08:50 AM

Hi! I am new to programming. How do I calculate the total elapsed or consumed time in Access? MS Excel has a custom time format [h]:mm:ss. When the total hours exceed or goes beyond 24 it just accumulates or continues on (ie. 28:04:34).

Is there a similar time format in Access? If none, how do I compute for the total time?

Thanks.

rjweers · March 23rd, 2005, 09:20 AM

There are quite a number of answers to your question depending on the task you're trying to accomplish.

Try looking in help for the DateDiff function.

Access is also able to simply subtract two dates.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

rdfernandez · March 23rd, 2005, 11:39 AM

rjweers, thanks for the suggestion but I am sorry if my request was not that clear.

What I am trying to do is to compute for the total duration of all calls per week for our PABX. When I do it in a query, the hours portion would return to 0 (zero) whenever the total hours is greater than 23. For example: if the total duration is 24 hours 13 mins and 10 sec (24:13:10) the result I get is 00:13:10. In Excel I just format the cell as [h]:mm:ss and I get the correct result (24:13:10).

Is there a way I can also do this in Access?

Thank you very much.

rgerald · March 23rd, 2005, 04:50 PM

When I had a similar problem in an application for calculating departmental telephone usage, I did the calculations in minutes and decimal minutes. I did not experience rollover in the totals.

Rand

rjweers · March 23rd, 2005, 10:40 PM

I'm afraid I'd go with Rand's idea.

In your query calculate the number of minutes for each record and sum that field. If you have to report in hours, split it out in your report.

Of course you could just query the data from Access into Excel, or use some other kind of Office automation for your calculation.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

rdfernandez · March 24th, 2005, 03:02 AM

Ok. Great suggestions!
Thanks for sharing your ideas.

:)

rgerald · March 24th, 2005, 11:51 AM

Here are the calculations for DecimalMinutes or DecimalHours whichever you prefer. Since seconds give the smallest granularity, they should be used in the DateDiff calculation.

Code:

SELECT DateDiff("s",[TimeOne],[TimeTwo])/60 AS DiffInDecimalMins, DateDiff("s",[TimeOne],[TimeTwo])/3600 AS DiffInDecimalHours
FROM TimeTest;

You will need to change table and field names as appropriate.

Rand