# Net working days, Date difference excluding alternate saturday

#### Hrishi

##### New Member
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

### 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
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
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
Loop

If dteInDate = dteCalcDate Then FirstThirdSat = True

dteCalcDate = dteCalcDate + 1[/FONT]
[FONT=Verdana]    'Second Saturday
Do While Weekday(dteCalcDate) <> vbSaturday
Loop

dteCalcDate = dteCalcDate + 1[/FONT]
[FONT=Verdana]    'Third Saturday
Do While Weekday(dteCalcDate) <> vbSaturday
Loop
If dteInDate = dteCalcDate Then FirstThirdSat = True
End Function[/FONT]``````

#### Tetra201

##### MrExcel MVP
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))

• LMacD