3rd Business Day

[Manuel]

I am trying to find out what the 3rd business day of the
month would be. I currently have a Work_Days function
which determines the amount of workdays in a month
excluding weekends and holidays. Any Ideas on how to
determine the third business day (i.e. for April 3rd
business day is 04/05/04). Attached is my Work_Days
funcion code:

Function Work_Days(BegDate As Date, EndDate As Date) As
Integer
Dim Days As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
Days = DateDiff("d", BegDate, EndDate)
DateCnt = BegDate + 1
EndDays = 0
Do While DateCnt <= EndDate
Select Case DateValue(DateCnt)
Case DateValue(#1/1/2004#)
EndDays = EndDays + 1
Case DateValue(#5/31/2004#)
EndDays = EndDays + 1
Case DateValue(#7/5/2004#)
EndDays = EndDays + 1
Case DateValue(#9/6/2004#)
EndDays = EndDays + 1
Case DateValue
(#11/25/2004#)
EndDays = EndDays + 1
Case DateValue
(#11/26/2004#)
EndDays = EndDays + 1
Case DateValue
(#12/24/2004#)
EndDays = EndDays + 1
Case DateValue
(#12/31/2004#)
EndDays = EndDays + 1
Case Else
If Format
(DateCnt, "ddd") = "Sun" Or _
Format
(DateCnt, "ddd") = "Sat" Then
EndDays = EndDays
+ 1
End If
End Select
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = Days - EndDays
End Function

 

[John Spencer (MVP)]

Define business day. Monday thru Friday?

To get the first day of the month based on a date

DateSerial(Year(SomeDate),Month(SomeDate),1)

Now adjust that depending on the day of the week (for Mon and Tues add 3, for
Wed add 5, Thurs add 5 For Friday add 5).

Use the Weekday function to get the day of the week and the DateAdd function to
add the requisite number of days.

So

DateAdd("d",IIF(Weekday(DateSerial(Year(SomeDate),Month(SomeDate),1))>3,3,5,DateSerial(Year(SomeDate),Month(SomeDate),1))

 

[Manuel]

Business day is Monday thru Friday. I tried your logic
below, but it has returned 04/04/04 instead of 04/05/04.
Is there something else that I need to add to calculate
the 3rd (Mon to Fri) business day?

Thanks,
Manuel

 

[John Spencer (MVP)]

Whoops!

I flipped the number to add AND I also missed calculating for Saturday and Sunday

I am out the door to a conference and haven't tested this replacement. You can
try it and see if it will work for you. You may have to adjust the parentheses
to get it to work and also the choices in the choose phrase.

DateAdd("d",CHOOSE(Weekday(DateSerial(Year(SomeDate),Month(SomeDate),1)),3,2,2,2,5,4,4),DateSerial(Year(SomeDate),Month(SomeDate),1))

 

[Manuel]

Thanks John, It worked.

-----Original Message-----
Whoops!

I flipped the number to add AND I also missed calculating for Saturday and Sunday

I am out the door to a conference and haven't tested this replacement. You can
try it and see if it will work for you. You may have to adjust the parentheses
to get it to work and also the choices in the choose phrase.
(SomeDate),1)),3,2,2,2,5,4,4),DateSerial(Year
(SomeDate),Month(SomeDate),1))

.