Adding new date column but excluding weekends

[chris54]

Hi everyone,

I've been using the code stated below for some time to create a new column for a "due date" which would be 30 days from the chosen input. The problem is that within those 30 days weekends are not being excluded meaning many of the dates are incorrect. Is there a way to integrate weekday or perhaps another function within my code so the "due date" calculated automatically excludes weekends?

Billdate = Application.InputBox("Enter 1st day of Current Month Billing eg 01-Jan-2015")
StartTRX = "<" & Billdate ' 01/10/15
EndTRX = Application.EoMonth(Billdate, 0) '31/10/15
EndTRX = ">" & EndTRX

StartDue = Application.EoMonth(Billdate, 0) ' 30/11/15

EndDue = Application.EoMonth(Billdate, 1) ' 31/10/15
StartDue = "<=" & StartDue ' due date
EndDue = ">" & EndDue ' due date =

Range("AZ3").Select
Selection.AutoFilter
Range("BC1").Select
ActiveSheet.Range("$A$3:$BA$70000").AutoFilter Field:=52, Criteria1:= _
StartTRX, Operator:=xlOr, Criteria2:=EndTRX
Range("AZ4").Select
Range(Selection, Selection.End(xlDown)).Select
'Range("AZ122").Select
'Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
ActiveSheet.Range("$A$3:$BA$70000").AutoFilter Field:=52
ActiveSheet.Range("$A$3:$BA$70000").AutoFilter Field:=53, Criteria1:= _
StartDue, Operator:=xlOr, Criteria2:=EndDue
Range("BA4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
ActiveSheet.Range("$A$3:$BA$70000").AutoFilter Field:=53
Range("AZ4").Select
pivots

 

[Joe4]

You can use the WORKDAY and NETWORKDAYS function in VBA like this:
Application.WorksheetFunction.Workday...
Application.WorksheetFunction.NetWorkdays...

I think you should be able to use those to do what you want.
Check out Microsoft's on-line help on those two functions if you are not familiar with them.