Calculate Time per Period: Type Mismatch

[onmyway]

Hi guys,

I am trying to calculate and add up the total time for a specific date period.

But I get a Run-time error: 13 - Type Mismatch

On this line:

WorkShift = WorkShift + Scell.Offset(0, 1)

i have attached a sample workbook.

Thank you in advance for all your help!

[onmyway]

Hi all,

i figured it out, but could still do with some help solving it:

Not Working: The format in my cell is 12:47. When you click on it, it also show 12:47 in the Formula Bar.
Working: The format in my cell is 12:47. When you click on it, it shows 12:47:00 PM in the Formula Bar.

How can i edit this code to work either way?

Thank you!

[joeu2004]

Not Working: The format in my cell is 12:47. When you click on it, it also show 12:47 in the Formula Bar.
Working: The format in my cell is 12:47. When you click on it, it shows 12:47:00 PM in the Formula Bar.
How can i edit this code to work either way?

The "times" in column C are text, not numeric time. To convert to numeric time, select C4:C18 and click on Data, Text To Columns, Finish.

PS: The Formula Bar always displays numeric time in the form 12:47:00 PM, regardless of the cell format.

[onmyway]

Hi joeu2004

Thanks for the feedback. I realize the format issues (like you explained). I was hoping to do the formatting via VBA, and not in the sheet. I want users to stay away from the sheet and the data. Safeguard it as much as possible.

This is the actual code I am using to format the time field to save it to the sheet in this format HH:MM

'TIME FORMAT
Private Sub txtWorkShiftStart_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim tString               As String
    
If Me.txtWorkShiftStart = "" Then
    MsgBox "Please enter a Work Start of Shift"
    Cancel = True
End If
    
    With txtWorkShiftStart
        ' don't process if blank
        If Len(.Value) <> 0 Then
        
            'Check if user put in a colon or not
            If InStr(1, .Value, ":", vbTextCompare) = 0 Then
                'If not, make string 4 digits and insert colon
                tString = Format(.Value, "0000")
                tString = Left(tString, 2) & ":" & Right(tString, 2)

                If IsDate(tString) Then
                    txtWorkShiftStart.Value = Format(TimeValue(tString), "HH:MM")
                Else
                    MsgBox "Not a valid time value"
                    Cancel = True
                End If
            Else
                If IsDate(.Value) Then
                    'Otherwise, take value as given
                    .Value = Format(.Value, "hh:mm")
                Else
                    MsgBox "Not a valid time value"
                    Cancel = True
                End If
            End If
            
        End If
        
    End With

End Sub

Perhaps any suggestions on how I can edit this to save in my desired format, but as numeric time?

Thank you!

[jindon]

Just change to

WorkShift = WorkShift + TimeValue(Scell.Offset(0, 1))

[onmyway]

Ah! Perfect, thank you!