Excel formula for counting continuous worked weekends, based on a start and end date

Question

I am looking for formula, which can count the continuous working weekends based on the start and end date.

I used frequency to count the countinuous data. However, not able to find continuity with the start and end date.

Sample data available in google drive https://drive.google.com/file/d/10QHVtKF5BcTx6vMTE7dtjE7r61cMPHX5/view?usp=sharing

Answer 1

I finally found a solution for this. I learned and written a macro to get the continuous working weekends based on the start and end date.

Here is the macro

     Option Explicit

     Sub Weekend_Calculation()
    ' Variables Declaration
    '*----------------------*
    Dim Cnum As Integer
    Dim Rnum As Integer
    Dim Rnum_t As Integer
    Dim i As Integer
    Dim D_diff As Integer
    Dim W_day As Integer
    Dim Counter As Integer
    Dim Counter_temp As Integer
    Dim StartDate As Date
    Dim EndDate As Date
    Dim Temp_date As Date
    Dim V_Sat As Date
    Dim V_Sun As Date
    Dim V_Sat_t As Double
    Dim V_Sun_t As Double
    Dim V_SatData As String
    Dim V_SunData As String

     ActiveWorkbook.Sheets("Workbookname").Select
     Range("A2").Select
     Rnum = Cells(Rows.Count, 1).End(xlUp).Row 'finding the last row
     Cnum = Cells(1, Columns.Count).End(xlToLeft).Column 'finding the last column

      Range("E2").Select
      Range(Cells(2, 5), Cells(Rnum, 6)).Select 'selecting the column E and F for clear the data
      Selection.Clear

       For i = 2 To Rnum 'For loop starts here
       If IsEmpty(Cells(i, 8)) Then 'First if condition starts here
        ActiveCell.Value = "No start date"
        ActiveCell.HorizontalAlignment = xlLeft
        ActiveCell.VerticalAlignment = xlCenter
        ActiveCell.Offset(1, 0).Select
        Else
         StartDate = Cells(i, 8)
        
                    If IsEmpty(Cells(i, 9)) Then 'Second if condition starts here
                        EndDate = Date - 1
                    Else
                        EndDate = Cells(i, 9) 'Second if condition ends here
                    End If
                    
       D_diff = DateDiff("d", StartDate, EndDate)
       If D_diff <= 7 Then 'Third if condition starts here
           ActiveCell.Value = "Not completed 7 days"
           ActiveCell.HorizontalAlignment = xlLeft
           ActiveCell.VerticalAlignment = xlCenter
           ActiveCell.Offset(1, 0).Select
       Else
            W_day = Weekday(StartDate) 'If ElseIf condtions starts here
                       If W_day = 1 Then
                         V_Sat = StartDate + 6
                        ElseIf W_day = 2 Then
                         V_Sat = StartDate + 5
                        ElseIf W_day = 3 Then
                         V_Sat = StartDate + 4
                        ElseIf W_day = 4 Then
                         V_Sat = StartDate + 3
                        ElseIf W_day = 5 Then
                         V_Sat = StartDate + 2
                        ElseIf W_day = 6 Then
                         V_Sat = StartDate + 1
                        Else
                         V_Sat = StartDate
                        End If 'If ElseIf condtions Ends here
            V_Sun = V_Sat + 1 'assinging the Sunday date
            Counter = 0
            Counter_temp = 0
            
            For Temp_date = V_Sat To EndDate
            'Assigning V_Sat and V_Sun to double variable
            V_Sat_t = V_Sat
            V_Sun_t = V_Sun

            'Hlookup function
            V_SatData = Application.HLookup(V_Sat_t, Range(Cells(1, 10), Cells(i, Cnum)), i, True)
            V_SunData = Application.HLookup(V_Sun_t, Range(Cells(1, 10), Cells(i, Cnum)), i, True)

            
            If V_SatData = "IN" And V_SunData = "IN" Then 'Fourth if condition starts here
                Counter = Counter + 1
            Else
                Counter = 0 'Counter resetting
            End If 'Fourth if condition ends here

            
            If V_SatData = "IN" And V_SunData = "IN" Then 'Fifth if condition starts here
                Counter_temp = Counter_temp + 2
            ElseIf V_SatData = "OUT" And V_SunData = "OUT" Then
                 Counter_temp = Counter_temp
            Else
                Counter_temp = Counter_temp + 1
             End If 'Fifth if condition ends here


            V_Sat = V_Sat + 7 'adding 7 for finding next Saturday
            V_Sun = V_Sun + 7 'adding 7 for finding next Sunday
            Temp_date = V_Sat ' Setting the Temp_date as next Saturday
            
            Next Temp_date
            
            Cells(i, 5).Value = Counter
            Cells(i, 5).HorizontalAlignment = xlCenter
            Cells(i, 5).VerticalAlignment = xlCenter
            Cells(i, 5).Offset(1, 0).Select
            
            Cells(i, 6).Value = Counter_temp
            Cells(i, 6).HorizontalAlignment = xlCenter
            Cells(i, 6).VerticalAlignment = xlCenter
            Cells(i, 6).Offset(1, 0).Select
                
                End If 'Third if condition ends here
        End If  'First if condition ends here

      Next i 'For loop ends here

      Range("A2").Select

      End Sub

Answer 2

I haven't found an easy way to do this. My approach is to develop an array of the dates for all the Sundays within the date range, then check if the employee is in on those weekends and use Frequency in the usual way to find the longest run of weekends worked.

The following separate formulas were used initially - it's easy to chain them together using spill formulas and # notation where appropriate:

Number of weekends in date range(in C6) - moving forward from start date to following Sunday, and backwards from finish date to previous Sunday unless finish date is a Saturday in which case move forward to Sunday immediately after it.

=(D3-(WEEKDAY(D3,2)<6)*WEEKDAY(D3,2)+(WEEKDAY(D3,2)=6)-(C3+7-WEEKDAY(C3,2)))/7+1

First Sunday in date range (in C7)

=C3+7-WEEKDAY(C3,2)

Array of Sunday dates in date range (E6)

=SEQUENCE(C6,1,C7,7)

Sundays where employee is in (G6)

=ISNUMBER(MATCH(E6#,IF(F3:CQ3="IN",F2:CQ2),0))

Saturdays where employee is in (H6)

=ISNUMBER(MATCH(E6#-1,IF(F3:CQ3="IN",F2:CQ2),0))

Weekends where employee is in on either Saturday or Sunday (I6) - this can easily be adjusted for both Saturday and Sunday using * operator

=SIGN(G6#+H6#)

Counts of successive weekends where employee is in (I have removed one weekend 16/17 Jan to show how this would work)

=FREQUENCY(IF(I6#,E6#),IF(I6#=0,E6#))

These can be combined into a single formula using Let

=LET(NWeekends,(D3-(WEEKDAY(D3,2)<6)*WEEKDAY(D3,2)+(WEEKDAY(D3,2)=6)-(C3+7-WEEKDAY(C3,2)))/7+1,FirstSunday,C3+7-WEEKDAY(C3,2),SundayDates,SEQUENCE(NWeekends,1,FirstSunday,7),
SundayMatches,ISNUMBER(MATCH(SundayDates,IF(F3:CQ3="IN",F2:CQ2),0)),SaturdayMatches,ISNUMBER(MATCH(SundayDates-1,IF(F3:CQ3="IN",F2:CQ2),0)),CombinedMatches,
SIGN(SaturdayMatches+SundayMatches),FREQUENCY(IF(CombinedMatches,SundayDates),IF(CombinedMatches=0,SundayDates)))

Then you would just need to use Max to get the longest run of weekends worked.