Help with Function

[Ccook]

I need a function that will return the first work day of the month. I have tried searching the posts but can't seem to find what I need. I'm sure it is out there somewhere.

Thanks in advance!


Cheryl Cook

 

[Fuga]

Not sure if there´s a simple function for this. Otherwize you could use a combination of

weekday (returning 5 or 6 (I think) for saturdays and sundays)
Month (returning 1 - 12)
Look it up in help!

and then some sql, for instance the first workday in januari would be (please don´t rely on the exact syntax)

select top 1 * from yourtable where month([datefield]) =1 and weekday([datefield]) <> 5 and weekday([datefield]) <> 6 order by datefield desc

Like I said: the syntax probably won´t work, but hopefully the idea will

Fuga.

 

[Ccook]

date function

Thanks for your reply. I have something like that now but it is not doing exactly what I wanted it to do. I will keep trying!

Cheryl

 

[antomack]

The following should do what you require:

Function LastBusDay(D As Variant) As Variant
  ' Returns the date of the last business day (Mon - Fri) in a month

  Dim D2 As Variant
  
  If VarType(D) <> 7 Then
    LastBusDay = Null
  Else
    D2 = DateSerial(Year(D), Month(D) + 1, 0)
    Do While WeekDay(D2) = 1 Or WeekDay(D2) = 7
      D2 = D2 - 1
    Loop
    LastBusDay = D2
  End If
  
End Function

Function FirstBusDay(D As Variant) As Variant
  ' Returns the date of the first business day (Mon - Fri) in a month

  Dim D2 As Variant
  
  If VarType(D) <> 7 Then
    FirstBusDay = Null
  Else
    D2 = DateSerial(Year(D), Month(D), 0) + 1
    Do While WeekDay(D2) = 1 Or WeekDay(D2) = 7
      D2 = D2 + 1
    Loop
    FirstBusDay = D2
  End If
  
End Function

 

[Ccook]

Thanks

Thanks for the help. I appreciate your help.....

Thanks again!
Cheryl