[RESOLVED] adding times from 4 different text boxes

[scottlafoy]

Hi, I have 4 different text boxes each contain a different time (8:00, 12:43, 22:12, 9:56) and I want to add up the total time in hours and minutes.

Private Sub Command6_Click()
On Error Resume Next

     If Len(Trim(lanaweek1.Text)) <> 0 And _
        Len(Trim(katieweek1.Text)) <> 0 And _
        Len(Trim(christinaweek1.Text)) <> 0 And _
        Len(Trim(fillweek1.Text)) <> 0 Then
            timetoadd = TimeValue(lanaweek1.Text) _
            + TimeValue(katieweek1.Text) _
            + TimeValue(christinaweek1.Text) _
            + TimeValue(fillweek1.Text)
            storeweek1 = Format(timetoadd, "hh:mm")
        End If
End Sub

without On Error Resume Next I get an error: type mismatch on lines:

timetoadd = TimeValue(lanaweek1.Text) _
            + TimeValue(katieweek1.Text) _
            + TimeValue(christinaweek1.Text) _
            + TimeValue(fillweek1.Text)

Thank you in advance for any suggestions.

[Ellis Dee]

Validate them using the IsDate() function instead of just verifying they aren't blank. IsDate("") returns False, so you don't need to also check for empty strings.

You can't really add times like that. Well, you can, but they roll over to a new day.

?TimeValue("8:00") + TimeValue("12:43") + TimeValue("22:12") + TimeValue("9:56")
1/1/1900 4:51:00 AM

Then you have to parse out how many days have elapsed. Much easier to just reduce everything to seconds, IMO.

Option Explicit

Public Function TimeToSeconds(pstrTime As String) As Long
    If IsDate(pstrTime) Then TimeToSeconds = Hour(pstrTime) * 3600 + Minute(pstrTime) * 60 + Second(pstrTime)
End Function

Public Function SecondsToTime(ByVal plngSeconds As Long) As String
    Dim strReturn As String

    If plngSeconds >= 3600 Then
        strReturn = plngSeconds \ 3600
        plngSeconds = plngSeconds Mod 3600
        strReturn = strReturn & ":" & Format(plngSeconds \ 60, "00") & ":" & Format(plngSeconds Mod 60, "00")
    ElseIf plngSeconds > 59 Then
        strReturn = plngSeconds \ 60 & ":" & Format(plngSeconds Mod 60, "00")
    Else
        strReturn = "0:" & Format(plngSeconds, "00")
    End If
    SecondsToTime = strReturn
End Function

Private Sub Command6_Click()
    Dim lngSeconds As Long
    
    ' Add up times
    lngSeconds = TimeToSeconds(lanaweek1.Text)
    lngSeconds = TimeToSeconds(katieweek1.Text) + lngSeconds
    lngSeconds = TimeToSeconds(christinaweek1.Text) + lngSeconds
    lngSeconds = TimeToSeconds(fillweek1.Text) + lngSeconds
    ' Format total as hh:nn:ss
    MsgBox SecondsToTime(lngSeconds)
End Sub

[scottlafoy]

I cant seem to get that to work correctly. I displays only the value from christinaweek1.

The main point of this is to keep track of how many hours a week the store is paying out to employee's. eg. if Christina worked 25:00 hours and Lana worked 15:00 hours and Katie worked 15:30 hours the total for the week would be 55:30 55 hours and 30 minutes.

[anhn]

This was solved once in one of your previous post.
Why you want to ask again? Just modify a little, that will work for you in this case.

Private Sub Command6_Click()
   Dim TotalTime As Date
   Dim t(1 To 4) As String, i As Integer
   
   t(1) = lanaweek1.Text
   t(2) = katieweek1.Text
   t(3) = christinaweek1.Text
   t(4) = fillweek1.Text
   For i = 1 To 4
      If IsDate(t(i)) Then
         TotalTime = TotalTime + TimeValue(t(i))
      Else
         MsgBox "Missing or Invalid Time entry"
         storeweek1 = ""
         Exit Sub
      End If
   End If
   storeweek1 = Int(TotalTime) * 24 + Hour(TotalTime) & Format(TotalTime, ":nn")
End Sub

[Code Doc]

Build a form with four text boxes in a control array. Add a label and a command button. Then appy this code:

Private Sub Command1_Click()
Dim Minutes As Integer, Seconds As Integer, Hours As Integer, TimeStr() As String
For I = 0 To 3
    TimeStr() = Split(Text1(I).Text, ":")
    Minutes = Minutes + CInt(TimeStr(0))
    Seconds = Seconds + CInt(TimeStr(1))
Next
Seconds = Seconds + 60 * Minutes
Hours = Seconds \ 3600
Minutes = (Seconds - Hours * 3600) \ 60
Seconds = Seconds - (Hours * 3600) - (Minutes * 60)
Label1.Caption = "Total Time = " & Hours & ":" & Minutes & ":" & Seconds
End Sub

Private Sub Form_Load()
Text1(0).Text = "38:00"
Text1(1).Text = "12:43"
Text1(2).Text = "22:12"
Text1(3).Text = "9:56"
End Sub

For checking purposes, I added a few minutes to your first observation to make sure that the sum of the times exceeded an hour. Does this look OK for you? Note that I make no checks for garbage in the text boxes.

[scottlafoy]

Hi, thanks for the replies. I did have a similar post solved before and I have been trying to make it work here but I have been un successful. Now I am able to add up the 4 times, although when they get to 92 hours and I add one more hour the hours jump down to 69 hours and not 93 as they should. I then add one more hour and it goes down to 46. I am trying to make it display 4 peoples combined times worked for one week, so potentially it needs to display 160 hours. The format I have it in is hours : minutes. So for example if Katie works 40 hours and 30 minutes this week and Christina works 40 hours and 15 minutes so far the weekly total should be 80:45, 80 hours and 45 minutes.

I will paste my code below

Private Sub storeTallyTimes(StartIndex As Integer, StopIndex As Integer, destTextBox As VB.TextBox)


   Dim subTime As Date, totalHrs As Long, totalMins As Long
    Dim i As Long
    For i = StartIndex To StopIndex
        If IsDate(t(i)) Then
            subTime = CDate(t(i))
            totalHrs = totalHrs + Hour(subTime)
            totalMins = totalMins + Minute(subTime)
        End If
    Next
    destTextBox.Text = totalHrs + totalMins \ 60 & ":" & totalMins - ((totalMins \ 60) * 60)
    End Sub

Private Sub Command6_Click()

     storeTallyTimes 0, 3, storeweek1
 
End Sub

I have tried some of the ideas posted above without any luck.

[Code Doc]

"I have tried some of the ideas posted above without any luck."
------------------
Looks like you forgot to try mine. It works and I know that it does.

[scottlafoy]

your code works as you intended it, I am just having trouble modifying it to suit my needs. It adds up the minutes and seconds but I am having trouble getting it to add up the hours. in your program input of 35:00 and 40:00 gives a result of 1:15:00. I cant put in hours put in hours minutes. I will take another look and try to modify it again.

[Code Doc]

your code works as you intended it, I am just having trouble modifying it to suit my needs. It adds up the minutes and seconds but I am having trouble getting it to add up the hours. in your program input of 35:00 and 40:00 gives a result of 1:15:00. I cant put in hours put in hours minutes. I will take another look and try to modify it again.

May I point out that in your OP or follow-up posts, you did not include hours. Nor did you include days nor years, nor minutes alone without any punctuation.

Output is only as good as input. Without standardizing your input, you are grasping at straws.

[scottlafoy]

Output is only as good as input. Without standardizing your input, you are grasping at straws.

my input for this is the output from another equasion. The format is always the same H:M (41:35). I have been able to get one of my attempts to work a little except when the hours get high enough they go back to 0. I am trying some code now. I will post back

[Ellis Dee]

What didn't work about the solution I posted?

[scottlafoy]

When I work with smaller # it works great. But when I try to add 30 hours (30:00) and 20 hours (20:00) I get 20 hours (20:00:00). My program is a schedual maker, it adds the hours for each employee to find a weekly total. Then it takes the weekly total for each employee and adds them up to find the store total for that week. It could be adding 40:00 hours per person per week. I need the added times displayed in hour and minute format (120:00) in order for the manager to see how many hours they have given out and if it falls under the amount allowed by the store.

[Ellis Dee]

Ah, that's the problem. Anything over "23:59" isn't a valid time, so TimeValue() won't work for you.

To correct that, use this better version of TimeToSeconds:

Public Function TimeToSeconds(pstrTime As String) As Long
    Dim lngHours As Long
    Dim lngMinutes As Long
    Dim lngPos As Long
    
    lngPos = InStr(pstrTime, ":")
    If lngPos = 0 Then Exit Function
    lngHours = Val(Left(pstrTime, lngPos - 1))
    lngMinutes = Val(Mid(pstrTime, lngPos + 1))
    TimeToSeconds = lngHours * 3600 + lngMinutes * 60
End Function

[scottlafoy]

thank you Ellis Dee, that is exactly what I wanted. I just edited it to remove the seconds being displayed.

from:

 strReturn = strReturn & ":" & Format(plngSeconds \ 60, "00") & ":" & Format(plngSeconds Mod 60, "00")

to:

 strReturn = strReturn & ":" & Format(plngSeconds \ 60, "00")

and it seems to work perfectly!

[Ellis Dee]

I just edited it to remove the seconds being displayed.

Oh yeah, heh, sorry, forgot about that. SecondsToTime() is a library function I wrote back in the 90s, so basically I never look at the code inside it.

Glad you got it configured for your purposes.