May 19 2022 01:59 PM - edited May 19 2022 02:05 PM
Hi there. I work at a company that meets with clients. We use their initials on the schedule.
Goal: Count the number of hours a client is scheduled.
Progress: I am able to count hours fairly effectively using the formulas =COUNTIF(B3:D3,"*AB*") or =SUMPRODUCT(--EXACT(B3:D3,$G$2)).
Issue: Some clients meet for 20 or 30 minute increments, and this is depicted in their cells using a slash symbol, where 1 slash mean 30 minutes and 2 slashes mean 20 minutes.
Question: How can I get an automatic count that considers the slashes as 30 minute increments (for 1 slash) or 20 minute increments (for 2 slashes), as shown in the first example below:
If the second example is possible, that'd be great, but I'm sure it's a lot more complex, so I'm not expecting a solution to it. Thanks in advance!
May 19 2022 03:37 PM
Sub customer()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim z As Double
For i = 3 To 6
For k = 7 To 9
For j = 2 To 4
If InStr(1, Cells(i, j), Cells(2, k), vbTextCompare) Then
Select Case Len(Cells(i, j)) - Len(Replace(Cells(i, j), "/", ""))
Case Is = 0
z = z + 1
Case Is = 1
z = z + 0.5
Case Is = 2
z = z + 0.33
End Select
Else
End If
Next j
Cells(i, k).Value = z
z = 0
Next k
Next i
End Sub
Maybe with these lines of code. You can click the button in cell L2 in the attached file to start the macro.
May 20 2022 04:40 AM
Please see attached workbook for a formula solution.