Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #9136

Re: calc first business day of month

Show all headers | View raw

On 3/12/2014 12:21 PM, bobh wrote:
> On Wednesday, March 12, 2014 12:38:27 PM UTC-4, bobh wrote:
>> Hi All, Anyone have vba code(they would share) that calculates the date of the first business day of a given month? thanks bobh.
>
> after working on it this is what I came up with
>     Dim DteHold As Date
>     DteHold = DateSerial(YEAR(Now), month(Now), 1)
>
>     If Weekday(DteHold) = vbSaturday Then
>        Me.Text2 = DateAdd("d", 2, DteHold)
>     ElseIf Weekday(DteHold) = vbSunday Then
>        Me.Text2 = DateAdd("d", 1, DteHold)
>     Else
>        Me.Text2 = DteHold
>     End If
>
> bobh.
>

That looks good. You could shorten it a little as follows:

	Dim DteHold As Date
	Dim intWeekday as Integer

	DteHold = DateSerial(YEAR(Now), month(Now), 1)
      	intWeekday = Weekday(DteHold, vbMonday)
	If intWeekday > 5 Then DteHold = DteHold + Abs((intWeekday-8))

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

calc first business day of month bobh <vulcaned@yahoo.com> - 2014-03-12 09:38 -0700
  Re: calc first business day of month bobh <vulcaned@yahoo.com> - 2014-03-12 10:21 -0700
    Re: calc first business day of month Neil <xxx@gmail.com> - 2014-03-12 16:26 -0500
  Re: calc first business day of month "Ron Paii" <None@none.com> - 2014-03-12 12:42 -0500
    Re: calc first business day of month bobh <vulcaned@yahoo.com> - 2014-03-12 12:02 -0700
      Re: calc first business day of month "Ron Paii" <None@none.com> - 2014-03-12 14:10 -0500
      Re: calc first business day of month "Ron Paii" <None@none.com> - 2014-03-12 14:21 -0500
  Re: calc first business day of month Patrick Finucane <patrickfinucanetx@gmail.com> - 2014-03-13 08:38 -0700
    Re: calc first business day of month kduc <kduc@numerifil.invalid> - 2014-03-13 17:22 +0100
      Re: calc first business day of month Patrick Finucane <patrickfinucanetx@gmail.com> - 2014-03-13 09:41 -0700
        Re: calc first business day of month "Phil" <phil@stantonfamily.co.uk> - 2014-03-13 18:40 +0000

csiph-web