How to get start and end time of each shift

[jose_graca]

I have a time shift table, where 0 means no activity (or day off) and values > 0 are different activities.

I can find the start hour of the first shift and the end hour of the last shift, but i'm struggling to find the last hour of the first shift and the first hour of the second shift.. The table should look like this:




Taking in account the example above, what i exactly want is a formula to determine that:

• On row 3, the end of the first shift is column S (or 14:00) and the beginning of the second shift is column V (or 15:30)
• On row 6, the end of the first shift is column U (or 15:00) and the beginning of the second shift is column X (or 16:30), and so on...

 

[Anthony47]

I prefer suggesting a "user function", that correspond to the following code:

Function ShiftInfo(ByRef HHours As Range, ByRef myTicks As Range, ByVal StEnd As Long, ByVal ShiftN As Long) As Variant
Dim oArr(), bArr(), eArr(), bRB, eRB
Dim I As Long, J As Long, mtCC As Long
'
'Returns Start and End of Working time
'   HHours is the header row
'   myTicks is the presence row(s)
'   StEnd: 0=both Begin & End; 1=Begin ;2=End
'   ShiftN=wich occourence look for
'
If StEnd > 2 Or StEnd < 0 Then
    ShiftInfo = CVErr(2200)
    Exit Function
ElseIf StEnd = 0 Then
    ReDim oArr(1 To myTicks.Rows.Count, 1 To 2)
Else
    ReDim oArr(1 To myTicks.Rows.Count, 1 To 1)
End If
mtCC = myTicks.Columns.Count
Set HHours = Application.Intersect(HHours.Cells(1, 1).EntireRow, myTicks.EntireColumn)
'
For I = 1 To myTicks.Rows.Count
    ReDim bArr(1 To mtCC)
    ReDim eArr(1 To mtCC)
    For J = 1 To mtCC
        If myTicks.Cells(I, J) = 1 And (J = 1 Or myTicks.Cells(I, J + 1 * (J <> 1)) = 0) Then bArr(J) = HHours.Cells(1, J)
        If myTicks.Cells(I, J) = 1 And (J = mtCC Or myTicks.Cells(I, J + 1) = 0) Then eArr(J) = HHours.Cells(1, J)
    Next J
    bRB = CVErr(2042): eRB = CVErr(2042)
    On Error Resume Next
        bRB = Application.WorksheetFunction.Small(bArr, ShiftN)
        eRB = Application.WorksheetFunction.Small(eArr, ShiftN)
    On Error GoTo 0
    If StEnd = 0 Then
        oArr(I, 1) = bRB: oArr(I, 2) = eRB
    ElseIf StEnd = 1 Then
        oArr(I, 1) = bRB
    ElseIf StEnd = 2 Then
        oArr(I, 1) = eRB
    End If
Next I
ShiftInfo = oArr
End Function

Copy the code into a "standard Module" of your vba project
Then return to Excel and you can use the "function" ShiftInfo

This function require 4 parametres:
-the heading with the hours (probably D2:AY2, in your example) (see Note**)
-the area with the presence (probably D3:AY9, in your example)
-a number to specify which information to search: 0=both Begin and End; 1=Begin; 2=End
-a number to specify which block to look at: 1=1st, 2=2nd, 3=3rd and so on

The function will return either 1 column (if only Begin or End are requested) or 2 columns; this multiplied by the number of rows that you specify as the presence area.
Since you have Office 365, all these columns and rows will be written on your sheet, if the cells are free (otherwise you'll have the "SPILL" error).

So for example you can write

=ShiftInfo(D1:F1;D2:AF8;0;2)

(see Note**)
This will return 7 rows and 2 columns, with both Begin and Ending of the second worked block in the period.
You need to manually format the output area as hours.

Note** Note that first parametre (the Header) will be aligned to the width of the presence area

HTH