CODEWITHSUNDEEP
excelvbadatefilter
Asen Panayotov
Asen Panayotov

Reputation: 3

Exclude weekend days when adding to current date VBA

I have the following piece of code, which is excluding all results in excel sheet that have date different than today + 6 days. The problem is that when I execute that on Monday I hit Sunday. I need to change it in a way that it will add always 6 days to my current date unless the result is Saturday or Sunday, then I would like to take the first working day after that, meaning - Monday. 

    Public Sub GRP_SC_Filter1()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    Dim DelDate As Long


    DelDate = DateSerial(Year(Date), Month(Date), Day(Date) + 6)


    LR = Sheets("Goods Receivable Planning").Range("A" & Rows.Count).End(xlUp).Row
    Cells.AutoFilter Field:=13, Criteria1:="<>" & DelDate

    ALR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    If ALR > 2 Then
        Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).Select
        Range("A2:A" & LR).Delete
        Range("A1").Activate
    End If
    Cells.AutoFilter
 '  MsgBox "Finished deleting rows"
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

I would be really thankful if somebody could help me transforming this into what I need. I am honestly stuck. Thank you in advance!

Upvotes: 0

Views: 7377

Answers (2)

FunThomas
FunThomas

Reputation: 29592

The function weekDay can hep you. The second parameter defines what day return 1, vbMonday would say that Mondays are 1, Saturday = 6, Sunday = 7

So, one way would be:

deldate = DateSerial(Year(Date + 3), Month(Date + 3), Day(Date + 3) + 6)
Do While (Weekday(deldate, vbMonday) >= 6)
    deldate = deldate + 1
Loop

Upvotes: 0

braX
braX

Reputation: 11755

This function should do what you want:

Function GetNextWorkingDay(dt As Date) As Date
  Select Case Weekday(dt)
    Case 1
      GetNextWorkingDay = DateAdd("d", 1, dt)
    Case 7
      GetNextWorkingDay = DateAdd("d", 2, dt)
    Case Else
      GetNextWorkingDay = dt
  End Select
End Function

Upvotes: 3

Related Questions