Forum Discussion
Counting time increments based on the number of times a text appears
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!
2 Replies
Please see attached workbook for a formula solution.
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 SubMaybe with these lines of code. You can click the button in cell L2 in the attached file to start the macro.
