summing time

[kevcar40]

Hi
i am importing a worksheet from excel
the workshhet contains data of work operations, faults that occurr the
start time, end time and duration of the stoppage

What i am trying to do sum the downtime against each operation for
each fault

the format of the data in the tables is
start time /06/2007 20:32:12
end time 05/06/2007 20:39:25
duration

i have wrote a query that sums the duration
problem is the time format the answer returns 03/01/1900 11:32:12

i would like it to show hh:nn:ss 83:47:59

can anyone point me in the right direction please

thanks

kevin

 

[Jeff Boyce]

It appears you are using an Access Date/Time field and expecting it to show
duration. Access Date/Time is used to hold "point-in-time" data.

If you need to display total durations, you'll need to come up with some
routines that add up the durations, then parse them to look like hh:mm:ss
(note: they will NOT be hh:mm:ss).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Kevin:

Because Access stores date/time data types as a 64 bit floating point number
as an offset from 30 December 1899 00:00:00 what you are getting is the
result of the summation of the underlying values expressed as a date. The
following function will show the duration of time between two date/time
values in the format h:nn:ss or optionally as # days h:nn:ss. Paste the
function into any standard module in your database:

Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double

dblDuration = dtmTo - dtmFrom

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY

TimeDuration = strDaysHours & strMinutesSeconds
Else
TimeDuration = lngHours & strMinutesSeconds
End If

End Function

Normally you'd pass two date/time values into the function, so you could
eliminate the redundant duration column in your table and call the function
in a query:

SELECT [start time], [end time],
TimeDuration([start time], [end time]) AS Duration
FROM YourTable;

To sum the durations you'd pass a zero as the first argument into the
function and then the summation of the end times minus the start times as the
second argument:

SELECT TimeDuration(0,SUM([end time]-[start time]))
AS TotalDuration
FROM YourTable;

If you want to show the duration in the format # days h:nn:ss pass True as
the optional third argument into the function, e.g.

SELECT TimeDuration(0,SUM([end time]-[start time]),True)
AS TotalDuration
FROM YourTable;

Ken Sheridan
Stafford, England

 

[mustabsic]

how are you doing

 

[kevcar40]

- Show quoted text -

Going quite well
how would i return the answer if the time value exceded 24 hours
in excel i would fomat the cell [h]:mm:ss

 

[Douglas J. Steele]

Going quite well
how would i return the answer if the time value exceded 24 hours
in excel i would fomat the cell [h]:mm:ss

Realistically, you shouldn't be using date/time fields to sum times: date
fields are intended strictly for timestamps (specific points in time), not
durations. You should be storing the durations as total seconds, and writing
your own function to format seconds to hh:mm:ss.

However, I do have a cheat available in my October, 2003 "Access Answers"
column in Pinnacle Publication's "Smart Access". You can download the column
(and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html