Net working days, Date difference excluding alternate saturday

Hrishi

New Member
Joined
Jan 25, 2017
Messages
38
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
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

New Member
Joined
May 18, 2019
Messages
21
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

Code:
[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.

Code:
[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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,542
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,100,187
Messages
5,473,023
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top