Adding Time

[math20]

Hi,

I am trying to sum fields with time (12:05:15 + 10:25:03). I know the total should add up to 22 Hours, 30 mins and 18secs but it adds up to a very diferent number. My guess is the format issue. Is there a way where i can get this done in a query where I just sum up the fields?

 

[raskew]

Hi -

Keep in mind that you're dealing with periods of elapsed time which doesn't follow the same rules as actual times in data/time data type. Give this a try:

Function ElapTimeAdd(pfirst As String, pnext As String) As String
'***********************************************
're:  [URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=1222502&page=1[/URL]
'Purpose:   Add two elapsed times and display result in hh:mm:ss format
'coded by:  raskew
'Inputs:    from debug (immediate) window
'           1) ? ElapTimeAdd("12:05:15", "10:25:03")
'           2) ? ElapTimeAdd("12:05", "10:25")

'Outputs:   1) 22:30:18
'           2) 22:30:00
'***********************************************

Dim firsthold As Long
Dim nexthold  As Long
Dim inthold   As Long
Dim inthours  As Integer
Dim intmins   As Integer
Dim intsecs   As Integer

'   1) Convert input to seconds and add
    firsthold = (3600 * Val(Left(pfirst, 2))) + (60 * Val(Mid(pfirst, 4, 2))) + Val(Mid(pfirst, 7, 2))
    nexthold = (3600 * Val(Left(pnext, 2))) + (60 * Val(Mid(pnext, 4, 2))) + Val(Mid(pnext, 7, 2))
    inthold = firsthold + nexthold

'   2) Convert inthold to elapsed hours, minutes and seconds
    inthours = inthold \ 3600
    intmins = (inthold Mod 3600) \ 60
    intsecs = (inthold Mod 3600) Mod 60
    
'   3) Format and output result
    ElapTimeAdd = Format(str(inthours), "00") & ":" & Format(str(intmins), "00") & ":" & Format(str(intsecs), "00")

End Function

HTH - Bob

 

[Remou]

This has a bit at the end:
Date Arithmetic in Queries
faq701-4230

 

[raskew]

Remou -

The poster's question was not about time as in date/time data type, but rather elapsed time, e.g. I worked 12 hours and 5 minutes yesterday and 10 hours and 25 minutes today for a total of 22 hours and 30 minutes.

I'm confused as to how the FAQ relates.

Bob

 

[Remou]

I thought:

I am trying to sum fields with time (12:05:15 + 10:25:03) ... My guess is the format issue. Is there a way where i can get this done in a query where I just sum up the fields?

Related to:

Summing times

If you sum entries in a date/time field that represent times you get a numeric result, e.g. if you have

2:34
3:45
2:50

the sum comes to 0.38125. To get a H:M:S result you might use Format([mytimes],"hh:nn:ss") ("nn" for minutes not "mm" which is month) but this gives the hours modulo 24. This adds a position for the number of days if your sum is going to exceed 24 hours:

dhhmmss: Str(Int(Sum([mytimes]))) & " " & Format(Sum([mytimes]),"hh:nn:ss")

I did not see this as an elaspsed time post. Also, the FAQ was about working with SQL and Math20 mentioned a query.