Time calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.

I have a database that runs a series of reports and log the time it takes
for each report in a table:

rs.addnew
rs("Time_Taken") = Time() - dteStartTime
rs.update

The idea is, I run a query on this table to derive the total amount of time
the reports take: sum([Time_Taken])

There were 50 reports, the average time was 2hrs each, but my query returns
09:04:34 as the total time! I have kind of added the times up in my head and
I get at least 57 hours!

Can anyone explain this or what I am doing wrong?

Cheers,
Steve.
 
The first thing you need to keep in mind that the result of adding or
subracting dates or times is not a date or time, but a duration. Date/Time
fields are not for storing durations, but points in time.

How you do the math will depend on how you want to present the duration. In
this case, I assume you want to present it in Hours, Minutes, and Seconds.
To do this, we first need to do the math correctly, then we need to format
the results to display it in Hours, Minutes, and Seconds.

The first issue is the correct math. You should always use the DateDiff
function for date/time math.

rs("Time_Taken") = Time() - dteStartTime
Should Be
rs("Time_Taken") = DateDiff("s", Time(), dteStartTime)

Now we have Time_Taken as a number of seconds. We use seconds because that
is the lowest common denominator we want to present. Now, to show it as
hours, minutes, and seconds, we need to do more math. Each hour will be 3600
seconds and each minute will be 60 seconds. Here is a format formula that
breaks it out into hours, minutes, and seconds.
Format(Time_Taken\3600,"00:") & Format((Time_Taken -
(Time_Taken\3600)*3600)\60,"00:") & Format(Time_Taken mod 60, "00")
 
FBxiii said:
I have a database that runs a series of reports and log the time it takes
for each report in a table:

rs.addnew
rs("Time_Taken") = Time() - dteStartTime
rs.update

The idea is, I run a query on this table to derive the total amount of time
the reports take: sum([Time_Taken])

There were 50 reports, the average time was 2hrs each, but my query returns
09:04:34 as the total time! I have kind of added the times up in my head and
I get at least 57 hours!
Click to expand...


You can not subtract two date/time value to get a time
value. The difference is a duration that needs to be
expressed in a specific unit of time.

Possibly, you want something more like:
rs("Time_Taken") = DateDiff("n", dteStartTime, Time())

which will save the number of minutes in the duration. If
needed the minutes can be displayed in hours/minutes by
using the expression:
Time_Taken \ 60 & ":" & Format(Time_Taken Mod 60, "00")
 
Back
Top