Net working days, Date difference excluding alternate saturday

[Hrishi]

Hello,

I have two columns for dates, Start date and end date.
I want to calculate difference in two dates but difference should be exclusive of all Sundays and all First and third Saturdays falling in between those two days. Plz ensure its not alternate Saturday its First and Third Saturday.

Can you help?

Regards

 

[steve the fish]

Re: Net workiong days, Date difference excluding alternate saturday

Thats a quite difficult calculation, for me at least. Id use a helper column/sheet with a named range containing all of the 1st and 3rd Saturdays. Then use that as your holiday range for the NETWORKDAYS. Place this formula in a cell and drag it down as far as you like:

=EOMONTH(DATE(2018,CEILING(ROWS($A$1:A1)/2,1),1),-1)+CHOOSE(WEEKDAY(EOMONTH(DATE(2018,CEILING(ROWS($A$1:A1)/2,1),1),-1)),6,5,4,3,2,1,7)+IF(MOD(ROWS($A$1:A1)+1,2),14,0)

This creates the list of 1st and 3rd Saturdays per month. Name the range 'holidays' then use:

=NETWORKDAYS.INTL(A1,B1,11,holidays)

where A1 and B1 contain your two dates to test. That i think should work.

 

[LMacD]

Re: Net workiong days, Date difference excluding alternate saturday

Hroshi, the calculation is complex and is better done in VBA rather than a formula in a cell.

This goes in the Microsoft Excel Objects worksheet

[FONT=Verdana]Option Explicit[/FONT]
[FONT=Verdana]'***************************************************************
'*                                                             *
'* Place this sub in code for the sheet the  date table is on  *
'*  Make the start date end date and count colums a table      *
'*  Name the table  "tblDates"                                 *
'*                                                             *
'***************************************************************[/FONT]
[FONT=Verdana]Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CellChanged As Range
    Dim StartCol As Integer
    Dim EndCol As Integer
    Dim ChangedCol As Integer
    Dim ChangedRow As Integer
   
    Set CellChanged = Target(1, 1)
    
    StartCol = Range("tblDates").Cells(1, 1).Column
    EndCol = Range("tblDates").Cells(1, 2).Column
    ChangedCol = CellChanged.Column
    ChangedRow = CellChanged.Row
    
    'This sub is triggered by a change to tthe worksheet.
    'I am only interested in a chnage to the start date or the end date
    
    'If not in the table exit
     If Intersect(CellChanged, Range("tblDates")) Is Nothing Then Exit Sub
      
    'If the changed col is not the date columns exit sub
    If ChangedCol <> StartCol And ChangedCol <> EndCol Then Exit Sub
    
    'If one enter is not a date
    If Not IsDate(Me.Cells(ChangedRow, StartCol)) Or _
            Not IsDate(Me.Cells(ChangedRow, EndCol)) Then Exit Sub
    
    Me.Cells(ChangedRow, EndCol + 1) = CountDays(Me.Cells(ChangedRow, StartCol), Me.Cells(ChangedRow, EndCol))
End Sub
[/FONT]

These two functions goes in the VBA projects Modules.

[FONT=Verdana]Option Explicit[/FONT]
[FONT=Verdana]'***************************************************************
'*                                                             *
'* Place these functions in a module in the workbook           *
'*                                                             *
'***************************************************************[/FONT]
[FONT=Verdana]Function CountDays(StartDate As Date, EndDate As Date)
    Dim intCounter As Integer
    Dim dteTemp As Date
    'first test StartDate is lower than EndDate
    If StartDate >= EndDate Then
        MsgBox "The start date must be before the end date"
        Exit Function
    End If
    dteTemp = StartDate
    CountDays = 0
    Do Until dteTemp = EndDate
        dteTemp = dteTemp + 1
        
        'Is this a Sunday
        If Format(dteTemp, "dddd") = "Sunday" Then GoTo NextDate
        
        'Is this the first Saturday?
        If FirstThirdSat(dteTemp) = True Then GoTo NextDate
        
        'Is this the third Saturday?
    
        'Increment intCounter
        CountDays = CountDays + 1
NextDate:
    Loop
End Function[/FONT]
[FONT=Verdana]Function FirstThirdSat(ByVal dteInDate As Date) As Boolean
    Dim dteCalcDate As Date
    FirstThirdSat = False
    dteCalcDate = DateSerial(Year(dteInDate), Month(dteInDate), 1)[/FONT]
[FONT=Verdana]    'This is the first Saturday
    Do While Weekday(dteCalcDate) <> vbSaturday
        dteCalcDate = DateAdd("d", 1, dteCalcDate)
    Loop
    
    If dteInDate = dteCalcDate Then FirstThirdSat = True
    
    dteCalcDate = dteCalcDate + 1[/FONT]
[FONT=Verdana]    'Second Saturday
    Do While Weekday(dteCalcDate) <> vbSaturday
        dteCalcDate = DateAdd("d", 1, dteCalcDate)
    Loop
    
    dteCalcDate = dteCalcDate + 1[/FONT]
[FONT=Verdana]    'Third Saturday
    Do While Weekday(dteCalcDate) <> vbSaturday
        dteCalcDate = DateAdd("d", 1, dteCalcDate)
    Loop
    If dteInDate = dteCalcDate Then FirstThirdSat = True
End Function[/FONT]

 

[Tetra201]

See if the following single-cell formula works for you:

=NETWORKDAYS.INTL(A1,B1,11)-SUMPRODUCT((DAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)))>{0,14})*(DAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)))<{8,22})*(WEEKDAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)))=7))

 

[James Clear]

See if the following single-cell formula works for you:

=NETWORKDAYS.INTL(A1,B1,11)-SUMPRODUCT((DAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)))>{0,14})*(DAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)))<{8,22})*(WEEKDAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)))=7))

Hi,

what if we have to exclude 3rd n 5th Saturday?? pls explain ur logic also

 

[Tetra201]

@ James Clear: See if the following formula works for you:

=NETWORKDAYS.INTL(A1,B1,11)-SUMPRODUCT(--(ROW(INDEX(A:A,A1):INDEX(A:A,B1))=WORKDAY.INTL(EOMONTH(ROW(INDEX(A:A,A1):INDEX(A:A,B1)),-1),{3,5},"1111101")))

 

[James Clear]

@ James Clear: See if the following formula works for you:

=NETWORKDAYS.INTL(A1,B1,11)-SUMPRODUCT(--(ROW(INDEX(A:A,A1):INDEX(A:A,B1))=WORKDAY.INTL(EOMONTH(ROW(INDEX(A:A,A1):INDEX(A:A,B1)),-1),{3,5},"1111101")))

Thanks for ur response,
Appreciate if u could explain ur logic