How to add time (120:15 + 10:15 = 130:30)?

[regava]

Access 2003 - Is there any function in VBA that would add time as in 120:15 + 10:15 = 130:30? Also, I need one for substract. I tested in excel and it can be done very simple. Any help is appreciated. Thank you.

 

[PHV]

A starting point:

a = Split("120:15", ":")
b = Split("10:15", ":")
c = 60 * (0 + a(0) + b(0)) + a(1) + b(1)
MsgBox (c \ 60) & ":" & (c Mod 60)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

Understand that Date and Time are numbers: Date is the integer part of the number and Time is the fractional part.

Taking two Date/Time values and calculating the difference will result in a DURATION that is expressed in DAYS.

So what does 120:15 + 10:15 mean?

You must be adding two DURATIONS, because it really does not make sense to ADD Two Time values. You might ADD a DURATION to a Date/Time value to get a new Date/Time value.

In your case, you will have to convert everything to minutes, do the math and convert to Hrs & Mins.

    Dim t1, t2, iMin As Integer, i As Integer, j As Integer
    
    For j = 1 To 2
        For i = 0 To 1
            If i = 0 Then
                Select Case j
                    Case 1
                        t1 = Split("120:15:00", ":")
                        iMin = t1(i) * 60
                    Case 2
                        t2 = Split("10:15", ":")
                        iMin = iMin + t2(i) * 60
                End Select
            Else
                Select Case j
                    Case 1
                        iMin = iMin + t1(i)
                    Case 2
                        iMin = iMin + t2(i)
                End Select
            End If
        Next
    Next
    MsgBox Int(iMin / 60) & ":" & iMin - Int(iMin / 60) * 60

The NUMERIC answer is 7830 Minutes.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[regava]

PHV and SkioVought thank you for your input. I got the yeast of what I have to do. By the way, 120:15 means 120 hours and 15 minutes. It is appreciated. Have a wonderful day.

 

[MajP]

So what does 120:15 + 10:15 mean?
You must be adding two DURATIONS, because it really does not make sense to ADD Two Time values.

I tested in excel and it can be done very simple

This kind of makes sense in Excel because Excel does heavy interpretation for you behind the scenes.

All times in excel (and vb) are based off of a base date and time. For excel it is midnight jan 1 1900. But times are stored as an integer part and a decimal part (you do not usually see this, but instead some formatting of this number). The integer part is how many days since jan 1 1900 and the decimal is the fraction of the day past midnight.

When you type in 120:15 Excel does the interpretation for you and assumes that you mean 120 hours 15 minutes past the base date and actually converts it to the date
5 Jan 1900 12:15 AM. You can therefore add these times likes durations because of this interpretation. Unfortunately I do not think in Access you can enter a field with 120:15 and have it convert to the equivalent of 5 days 15 minutes passed the base date like it does in Excel.

Thus you are stuck with the heavy lifting proposed above.

 

[regava]

I want to thank all of you. I came up with the following function which solved my problem. I hope it helps

Private Function AddSubTime(stTim1 As String, stTim2 As String, SA As String) As String
'stTim1 is the first time as "hhh:mm" where hhh > 0 and mm = 0-59
'stTim2 is the second time as "hhh:mm"
'SA = A for adding: stTim1 + stTim2
'SA = S for substracting: stTim1 - stTim2
'Returns the Result of the operation as "hhh:mm" or "ERROR"
Dim X As Variant
Dim Y As Variant
Dim a, b, c, d, e, f As Long

If IsNull(stTim1) Then
  MsgBox "Missing First Argument"
  AddSubTime = "ERROR"
  Exit Function
End If
X = Split(stTim1, ":")
a = CLng(X(0))
c = CLng(X(1))
If c > 59 Then          '0 - 59
  MsgBox "Error in First Argument"
  AddSubTime = "ERROR"
  Exit Function
End If

If IsNull(stTim2) Then
  MsgBox "Missing Second Argument"
  AddSubTime = "ERROR"
  Exit Function
End If
Y = Split(stTim2, ":")
b = CLng(Y(0))
d = CLng(Y(1))
If d > 59 Then          '0 - 59
  MsgBox "Error in Second Argument"
  AddSubTime = "ERROR"
  Exit Function
End If

If SA = "A" Then        'Substracting or Adding
  f = c + d             'We are Adding
  e = 0
  If f >= 60 Then
    e = 1
    f = f - 60
  End If
  AddSubTime = Trim(CStr(a + b + e)) & ":" & Trim(CStr(f))
Else                    'We are Substracting
  If a < b Then
    MsgBox "First Argument must be greater than Second Argument"
    AddSubTime = "ERROR"
    Exit Function
  End If
  If c < d Then
    a = a - 1
    c = c + 60
  End If
  AddSubTime = Trim(CStr(a - b)) & ":" & Trim(CStr(c - d))
End If
End Function

 

[SkipVought]

Please be concise with your TERMS.

'stTim1 is the first time as "hhh:mm" where hhh > 0 and mm = 0-59
'stTim2 is the second time as "hhh:mm"

These are NOT "times" but rather DURATIONS.

Times are reference POINTS.

It would be akin to saying,

'stDte1 is the first date as "mm:dd" where mm > 0 and dd = 0-29
'stDte2 is the second Date as "mm:dd"

Also, if you expect these declarations to ALL be declared as LONG, only the LAST one is LONG. All the others are Variants.

Dim a, b, c, d, e, f As Long

I believe that you want...

Dim a As Long, b As Long, c As Long, d As Long, e As Long, f As Long

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MajP]

Also a string can never be null therefore this does nothing
If IsNull(stTim1) Then
MsgBox "Missing First Argument"
AddSubTime = "ERROR"
Exit Function
End If

You could either change
stTim1 As Variant, stTim2 As Variant

or
if stTim1 =

 

[MajP]

Also I would probably first check that the arguments are in the correct format

Ensure that it is of the format ##:##
1)check first for the ":"
instr ":"
this will also ensure that it is not ""

then split it
then check that A isnumeric or before converting
then check that B isnumeric before converting

Bad data cases
""
12a:34
123
:45 (not sure if you enter data like this because I would think in order to split it needs) 0:45

 

[regava]

Skip:
It is a matter or semantics. In my particular case StTim1 refers to the number of hours and minutes that a person has accumulated for vacation or sick time, and stTim2 is the number of hours and minutes that the person earned (add) or the person used (substract). You are absolutely right about the declaration of a , b, c, d, e, and f.
MajP:
Thank you for pointing out the deficiencies. I have already implemented all of them.
I want to thank both for your insight in this matter and convey my gratitude for your help.