First week or business day of the month

[Dutch1956]

Hi,



as a novice I was able to find below code on the internet. It works great, it results in giving me the first Monday of the month. What do I need to change for the Function to return the first week or business day?
Any help is very much appreciated.


Public Function FirstMonday(Optional InputDate As Variant) As Date
Dim i As Integer
Dim dteTemp As Date

If IsMissing(InputDate) Or Not IsDate(InputDate) Then
dteTemp = DateAdd("m", 1, Date)
Else
dteTemp = DateAdd("m", 1, InputDate)
End If

For i = 1 To 7
dteTemp = DateSerial(Year(dteTemp), Month(dteTemp), i)
If Weekday(dteTemp) = vbMonday Then
FirstMonday = dteTemp
Exit For
End If
Next i
End Function

Bert,
Swansea, UK

[RuralGuy]

Wouldn't that still be the 1st Monday of the month or are you wanting to adjust for that day being a holiday?

[Dutch1956]

Thank you for the quick reply.
I am looking for that day to be the first working day of the Month and with working day I mean Monday thru Friday. That would make it for September Thursday 01/09/11, for October it would be 03/10/2011, and for November 01/11/11, etc. With other words I am looking to find the first date of the month as long as it is not a Saturday or Sunday.

Thanks for your help.

[RuralGuy]

D'oh! Time for a little more coffee. That task should be pretty easy. Be right back.

[RuralGuy]

This should do it:

Public Function FirstWorkDay(Optional InputDate As Variant) As Date
 
   If IsMissing(InputDate) Or Not IsDate(InputDate) Then
      FirstWorkDay = DateSerial(Year(Date), Month(Date) + 1, 1)
   Else
      FirstWorkDay = DateSerial(Year(InputDate), Month(InputDate) + 1, 1)
   End If
   Do Until Weekday(FirstWorkDay, vbMonday) < 6
      FirstWorkDay = FirstWorkDay + 1
   Loop
   
End Function

EDIT: My bad...be right back.

[RuralGuy]

Code in post #5 should now do what you want.

[ajetrumpet]

This should do it:

Public Function FirstWorkDay(Optional InputDate As Variant) As Date
 
   If IsMissing(InputDate) Or Not IsDate(InputDate) Then
      FirstWorkDay = DateSerial(Year(Date), Month(Date) + 1, 1)
   Else
      FirstWorkDay = DateSerial(Year(InputDate), Month(InputDate) + 1, 1)
   End If
   Do Until Weekday(FirstWorkDay, vbMonday) < 6
      FirstWorkDay = FirstWorkDay + 1
   Loop
   
End Function

EDIT: My bad...be right back.

Allan,

how long has the function ISMISSING() been in Access? I didn't know it had such a function. thanks.

[alansidman]

Adam;
New for me, too.

http://office.microsoft.com/en-us/ac...001228866.aspx

[Dutch1956]

Hi RuralGuy,

I hope you enjoyed your coffee because you deserve every drop of it. Your code works exactly the way I want it. Thank you so much.

Bert,
Rainy Swansea, UK.

[RuralGuy]

Allan,

how long has the function ISMISSING() been in Access? I didn't know it had such a function. thanks.

I don't know but it is in Access XP.

[RuralGuy]

Hi RuralGuy,

I hope you enjoyed your coffee because you deserve every drop of it. Your code works exactly the way I want it. Thank you so much.

Bert,
Rainy Swansea, UK.

Glad we could help Bert.

[ajetrumpet]

Adam;
New for me, too.

http://office.microsoft.com/en-us/ac...001228866.aspx

so when you apply this technique:

Optional MyVar As String = "specialvalue"

do you have to provide a value for the optional? I pretty much always do this to check for missings:

Optional MyVar As Boolean

if MyVar then
   'value was provided
else
   'value was NOT
end if

so the question is, can you use ISMISSING() for types other than variants? and if you don't have Acc 2007 and above, do you have to provide a sentinel value for optionals if you want to check for their existence?

[RuralGuy]

I believe a visit to the help files and MSDN is in order here.