Adding Working Days to Date

Hi Breanna,

Dates are always a problem to work with.  I decided to use a custom function to solve your problem.  The function will take the date field and the number of days from the end of the prior month (beginning of the current month).  It return 1 if the date falls within those business dates, if not it returns a 0.  Let me know if you need instructions on how to install a custom function, basically just put it in your custom functions folder of you current project or in the Local Library.  Here is the code I used for the function.

Option Explicit
Function GreaterThanXBusDays(DateField As Date, Days As Double) As Double
	Dim iCurrentMonth As Integer
	Dim iCurrentYear As Integer
	Dim i As Integer
	Dim testDate As Date
	Dim iDateCount As Integer
	
	'if the number of business days is larger than 20 than return 0
	If days > 20 Then
		GreaterThanXBusDays = 0
		Exit Function
	End If
	
	'get the current month
	iCurrentMonth = Month(Now())
	iCurrentYear = Year(Now())

	'if date is different year or month return 0
	If iCurrentMonth = Month(DateField) and iCurrentYear = Year(DateField) Then
		'calculate the last business date for the number of days (excludes Saturdasy and Sundays)
		For i = 1 To 31
			testDate = DateSerial(iCurrentYear, iCurrentMonth, i)
			If Weekday(testDate) <> 1 And Weekday(testDate) <> 7 Then
				iDateCount = iDateCount + 1
				If iDateCount = Days Then
					Exit For
				End If
			End If
		Next i 
	Else
		GreaterThanXBusDays = 0
		Exit Function
	End If
	If DateField <= testDate Then
		GreaterThanXBusDays = 1
	Else
		GreaterThanXBusDays = 0
	End If
End Function

Thank you, Brian! I really appreciate the help. I was able to download this and save it to my custom functions, but when I ran a test it's giving me all 0s where I would expect to see 1. Do you know if I might be doing something wrong or what updates I can make to the code?

Also, is there a way it can take the month end based on another date in the dataset rather than automatically taking prior month? I realized I'll be working with multiple months at a time when this is in production, so for example if I have Q1 data my accounting date might be 2/15 then I'd want to see if my post date is 4 business days from 2/28. Or my accounting date might be 3/28 then I'd want to see if my post date is 4 business days from 3/31. Thanks again!

Hi there, can you give me the list of dates that you expected to get a 1?  Right now it always defaults to the beginning of the month, so if you ran the script today it would be using today as the starting point.  

If I add a month parameter and used that for the calculation instead of the current date, would that work for you?

Hi Breanna,

Here is an update.  I have added a third parameter that will take a date ("YYYYMMDD") and use the year and month from the date to do the analysis. 

Let me know if that meets your needs.

Brian