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

Groups > comp.databases.ms-access > #9123 > unrolled thread

calc first business day of month

Started bybobh <vulcaned@yahoo.com>
First post2014-03-12 09:38 -0700
Last post2014-03-13 18:40 +0000
Articles 11 — 6 participants

Back to article view | Back to comp.databases.ms-access

Contents

  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

#9123 — calc first business day of month

Frombobh <vulcaned@yahoo.com>
Date2014-03-12 09:38 -0700
Subjectcalc first business day of month
Message-ID<a2d838e8-37a2-4ecd-97ea-ab6de0c16661@googlegroups.com>
Hi All,

Anyone have vba code(they would share) that calculates the date of the first business day of a given month?
thanks
bobh.

[toc] | [next] | [standalone]

#9125

Frombobh <vulcaned@yahoo.com>
Date2014-03-12 10:21 -0700
Message-ID<11a1e8b1-a582-4f4c-a1bc-baf299608d1b@googlegroups.com>
In reply to#9123
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.

[toc] | [prev] | [next] | [standalone]

#9136

FromNeil <xxx@gmail.com>
Date2014-03-12 16:26 -0500
Message-ID<lfqjdc$1k1$1@dont-email.me>
In reply to#9125
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))

[toc] | [prev] | [next] | [standalone]

#9126

From"Ron Paii" <None@none.com>
Date2014-03-12 12:42 -0500
Message-ID<lfq69p$l1b$1@dont-email.me>
In reply to#9123
datevalue(dateadd("d",2-weekday(now),now))

"bobh" <vulcaned@yahoo.com> wrote in message 
news:a2d838e8-37a2-4ecd-97ea-ab6de0c16661@googlegroups.com...
> Hi All,
>
> Anyone have vba code(they would share) that calculates the date of the 
> first business day of a given month?
> thanks
> bobh.

[toc] | [prev] | [next] | [standalone]

#9129

Frombobh <vulcaned@yahoo.com>
Date2014-03-12 12:02 -0700
Message-ID<a0e13c4a-e15b-40b5-a9a8-d672114b7289@googlegroups.com>
In reply to#9126
On Wednesday, March 12, 2014 1:42:15 PM UTC-4, Ron Paii wrote:
> datevalue(dateadd("d",2-weekday(now),now)) "bobh" <vulcaned@yahoo.com> wrote in message news:a2d838e8-37a2-4ecd-97ea-ab6de0c16661@googlegroups.com... > Hi All, > > Anyone have vba code(they would share) that calculates the date of the > first business day of a given month? > thanks > bobh.

thanks for the quick reply and code but, this returns the Monday date of the current week your in. I was looking for the first business day date for the current month I'm in.
bobh.

[toc] | [prev] | [next] | [standalone]

#9130

From"Ron Paii" <None@none.com>
Date2014-03-12 14:10 -0500
Message-ID<lfqbfk$vnm$1@dont-email.me>
In reply to#9129
datevalue(dateadd("d",vbmonday-weekday(dateserial(year(now),month(now),1)),dateserial(year(now),month(now),1)))


"bobh" <vulcaned@yahoo.com> wrote in message 
news:a0e13c4a-e15b-40b5-a9a8-d672114b7289@googlegroups.com...
> On Wednesday, March 12, 2014 1:42:15 PM UTC-4, Ron Paii wrote:
>> datevalue(dateadd("d",2-weekday(now),now)) "bobh" <vulcaned@yahoo.com> 
>> wrote in message 
>> news:a2d838e8-37a2-4ecd-97ea-ab6de0c16661@googlegroups.com... > Hi All, > 
>>  > Anyone have vba code(they would share) that calculates the date of the 
>>  > first business day of a given month? > thanks > bobh.
>
> thanks for the quick reply and code but, this returns the Monday date of 
> the current week your in. I was looking for the first business day date 
> for the current month I'm in.
> bobh.

[toc] | [prev] | [next] | [standalone]

#9131

From"Ron Paii" <None@none.com>
Date2014-03-12 14:21 -0500
Message-ID<lfqc3e$5dr$1@dont-email.me>
In reply to#9129
iif(weekday(dateserial(year(now),month(now),1))=vbsaturday,dateadd("d",2,dateserial(year(now),month(now),1)), 
_
iif(weekday(dateserial(year(now),month(now),1))=vbsunday,dateadd("d",1,dateserial(year(now),month(now),1)),dateserial(year(now),month(now),1)))

'1st Monday of month

"bobh" <vulcaned@yahoo.com> wrote in message 
news:a0e13c4a-e15b-40b5-a9a8-d672114b7289@googlegroups.com...
> On Wednesday, March 12, 2014 1:42:15 PM UTC-4, Ron Paii wrote:
>> datevalue(dateadd("d",2-weekday(now),now)) "bobh" <vulcaned@yahoo.com> 
>> wrote in message 
>> news:a2d838e8-37a2-4ecd-97ea-ab6de0c16661@googlegroups.com... > Hi All, > 
>>  > Anyone have vba code(they would share) that calculates the date of the 
>>  > first business day of a given month? > thanks > bobh.
>
> thanks for the quick reply and code but, this returns the Monday date of 
> the current week your in. I was looking for the first business day date 
> for the current month I'm in.
> bobh.

[toc] | [prev] | [next] | [standalone]

#9147

FromPatrick Finucane <patrickfinucanetx@gmail.com>
Date2014-03-13 08:38 -0700
Message-ID<78bcfa7e-665c-45b4-9e1f-3a5cbcb6e182@googlegroups.com>
In reply to#9123
On Wednesday, March 12, 2014 11:38:27 AM UTC-5, 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.

dat = Date()
 ?  (dat - Day(dat) + 1) + 7-Weekday(dat - Day(dat), vbMonday)

[toc] | [prev] | [next] | [standalone]

#9149

Fromkduc <kduc@numerifil.invalid>
Date2014-03-13 17:22 +0100
Message-ID<lfslvs$13q7$1@talisker.lacave.net>
In reply to#9147
Le 13/03/2014 16:38, Patrick Finucane a écrit :
> On Wednesday, March 12, 2014 11:38:27 AM UTC-5, 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.
> 
> dat = Date()
>  ?  (dat - Day(dat) + 1) + 7-Weekday(dat - Day(dat), vbMonday) 

Don't forget légal holidays, etc...


-- 
kd

[toc] | [prev] | [next] | [standalone]

#9150

FromPatrick Finucane <patrickfinucanetx@gmail.com>
Date2014-03-13 09:41 -0700
Message-ID<c809e3be-cb61-4e23-88a9-3b5952b4dfb5@googlegroups.com>
In reply to#9149
On Thursday, March 13, 2014 11:22:12 AM UTC-5, kduc wrote:
> Le 13/03/2014 16:38, Patrick Finucane a écrit : > On Wednesday, March 12, 2014 11:38:27 AM UTC-5, 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. > > dat = Date() > ? (dat - Day(dat) + 1) + 7-Weekday(dat - Day(dat), vbMonday) Don't forget légal holidays, etc... -- kd

Are there any holidays that would occur on the first Monday of a month?

[toc] | [prev] | [next] | [standalone]

#9152

From"Phil" <phil@stantonfamily.co.uk>
Date2014-03-13 18:40 +0000
Message-ID<lfsu3o$qef$1@speranza.aioe.org>
In reply to#9150
On 13/03/2014 16:41:13, Patrick Finucane wrote:
> On Thursday, March 13, 2014 11:22:12 AM UTC-5, kduc wrote:
>> Le 13/03/2014 16:38, Patrick Finucane a écrit : > On Wednesday, March 1
> 2, 2014 11:38:27 AM UTC-5, 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. > > dat = Date() > ? (dat - Day(dat) + 1) +
> 7-Weekda y(dat - Day(dat), vbMonday) Don't forget légal holidays, etc...
> -- kd
> 
> Are there any holidays that would occur on the first Monday of a month?
> 

Depends which country you are in.
UKEaster Monday & First May bank holiday.
Scotland January First

These may help

Public Function EasterDate(Yr As Integer) As Date
     
    Dim Da As Integer
    Da = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterDate Date = DateSerial(Yr, 3, 1) + Da + (Da > 48) + 6 - ((Yr + Yr \ 4 +
_     Da + (Da > 48) + 1) Mod 7)
     
End Function

Public Function EasterMonday(Yr As Integer) As Date
    
    EasterMonday = DateAdd("d", 1, EasterDate(Yr))
    
End Function

Public Function FirstSpringHoliday(Yr As Integer) As Date

FirstSpringHoliday iday = DateSerial(Yr, 5, ((7 - Weekday(DateSerial(Yr, 5,
7))) + 2) Mod 7) 'DateSerial(Year(mydte), Month(mydte), ((7 -
Weekday(DateSerial(Year(mydte), Month(mydte), 7))) + 2) mod 7)

End Function

Public Function NewYearsDay(Yr As Integer) As Date

    NewYearsDay = DateSerial(Yr, 1, 1)
    
End Function

Phil

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web