Determining First Business Day of Month

[byrne1]

Does anyone know of a way to determine whether or not the current date is the first business day of the month?

 

[FindersKeepers]

Here is a function that will determine the first Monday of any given month. It would need to be modified if you have a fiscal calendar or need to account for holidays ...

Public Function FirstBusinessDay(dteDate As Date) As Date
' Purpose: Find First Business Day of Month
' Returns: First Monday of Month

' Assumes First Business Day is First Monday of
' Calendar Month of the dteDate passed variable
' ... this means that it does not handle Business
' Fiscal calendars that do not begin on first
' Calendar day, and it does not taken into
' account business Holidays ... every business
' could have a different fiscal and holiday
' calendar

' Get First Monday of Month
Select Case Weekday(DateSerial(Year(dteDate), Month(dteDate), 1))
Case vbSunday
FirstBusinessDay = DateSerial(Year(dteDate), Month(dteDate), 2)
Case vbMonday
FirstBusinessDay = DateSerial(Year(dteDate), Month(dteDate), 1)
Case vbTuesday
FirstBusinessDay = DateSerial(Year(dteDate), Month(dteDate), 7)
Case vbWednesday
FirstBusinessDay = DateSerial(Year(dteDate), Month(dteDate), 6)
Case vbThursday
FirstBusinessDay = DateSerial(Year(dteDate), Month(dteDate), 5)
Case vbFriday
FirstBusinessDay = DateSerial(Year(dteDate), Month(dteDate), 4)
Case vbSaturday
FirstBusinessDay = DateSerial(Year(dteDate), Month(dteDate), 3)
Case Else
' Do Nothing
End Select
End Function

 

[strongm]

If all you want to do is find the first monday of any given month, then there are a lot of ways of achieving this. Here's another alternative (it is shorter than FindersKeepers, but slightly more obscure in how it works):
[tt]
Private Function FirstMonday(dteDate As Date) As Date
Dim FirstMondayModifier As Long
Dim FirstDayOfMonth As Date

FirstDayOfMonth = DateSerial(Year(dteDate), Month(dteDate), 1)

FirstMondayModifier = (9 - Weekday(FirstDayOfMonth)) Mod 7
FirstMonday = (DateAdd("d", FirstMondayModifier, FirstDayOfMonth))
End Function

 

[MichaelRed]

I think there is much ado about "Monday". byrne only asked to know " ... whether or not the current date is the first business day of the month? ... "

It looks -to me- like the responses provided above are answering a different question. Still, I think there is some merit in generalization of procedures, so I did incorporate an optional call argument so any date could be checked, but (of course!) set it to default to the current system date if none were provided.

I believe the following specifically answere the original inquiry with a simple boolean, and expaned on the theme a tiny bit to allow the similar inquiry for any (valid) date.

Public Function BusDay1(Optional dtIn As Date) As Boolean

    'whether or not the current date is the first business day of the month

    Dim FirstOfMnth As Date

    If (CLng(dtIn) = 0) Then
        dtIn = Date
    End If

    FirstOfMnth = DateSerial(Year(dtIn), Month(dtIn), 1)
    Do While Weekday(FirstOfMnth) = vbSunday Or Weekday(FirstOfMnth) = vbSaturday
        FirstOfMnth = FirstOfMnth + 1
    Loop

    If (FirstOfMnth = dtIn) Then
            BusDay1 = True
    '     Else
    '       BusDay1 = False
    End If

End Function
[code]

 MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WilMead]

there is NO need for loops or logic.

Given Sunday = 0, Monday = 1 and True = -1

You want to add values to 1 based on the weekday it falls on.
[tt]
First is use To determine
Sunday 1 + 0 + 1, Monday is 2
Monday 1 + 0 + 0, Monday is 1
Tuesday 1 + 7 - 1, Monday is 7
Wednesday 1 + 7 - 2, Monday is 6
etc.
[/tt]
Assume that DOW = Weekday(FirstOfMonth)

The 0 and 7 value can be generated with
[tt](-7 * (DOW <= Monday)) [/tt]

Generate the liner series 1, 0, -1, -2... with
[tt]1 - DOW [/tt]

Your algorithm for First Monday becomes:
[tt](-7 * (DOW <= Monday)) + ( 1 - DOW)[/tt]



Wil Mead
wmead@optonline.net

 

[strongm]

Psst...don't mean to be pedantic, but:

1. You are using logic (just encapsulating it, rather than making it explicit)
2. Your encapsualted logic function is inverted
3. VB by default treats Sunday as 1, not 0

On the other hand, it does have the virtue that the final algorithm is possibly even more opaque than my [tt](9 - DOW) Mod 7 [/tt] variant...

 

[petermeachem]

Isn't it simpler than that. One of the first 7 days of a month must be a Monday, so if today is a Monday and the date is less than 8, then it must be the first one.

Peter Meachem
peter@accuflight.com

 

[strongm]

Ah - but the question we've been answering (although, as MichaelRed has pointed out, it may not be the question that was being asked) is &quot;Given any month in any year, on what date did the first Monday fall?&quot;, as opposed to &quot;Given a date known to be a Monday, is it the first one in the month?&quot;

 

[petermeachem]

I always find it useful to answer the question that was asked, which is why I got good exam results at school.
Peter Meachem
peter@accuflight.com

 

[WilMead]

1. Maybe I should have said IfThenElses... Ehh who really cares?
2. OOps! My bad, I wondered why there was an extra character. It didn''t feel right when I typed it. I should have known. )

3. That's the good one.

Accomodating for a shift of Sunday value changes forces us to substitute the name for the other &quot;1&quot; in the original formula giving us:
[tt]
(-DaysInWeek * (DOW > Monday)) + (Monday - DOW)
[/tt]
Modulo allows us to factor the &quot;logic&quot; out resulting in (DaysInWeek + Monday - DOW) MOD 7 or Your

(9 - DOW) Mod 7 for the date offset.

Now it becomes apparent how adapt to do Tuesdays or Fridays or for that matter any given day of the week.


Strongm, you had the MOD connection, so I left it to you to explain how it helps.



Go Pete!

IsFirstMonday = (Monday = DOW) and (Day < 8)


Give a man a fish and you feed him for a day.
Teach him how to fish and you feed his village for...



Wil Mead
wmead@optonline.net

 

[Sawatzky]

Dear all,

pmfyi

but just some additional ideas to that:

the question was : whether or not the current date is the first business day of the month

It was so far answered how to determine in any form the first monday of the month (which is really nice to see so many different algorithms for that)
But what if the year starts with a monday on first of january?? Then you have a monday as first day of a month which is not a business day.
I do not know all the local vacation day's , but I think there are a lot of them .
What about the first business day being a tuesday.
Or is saturday a business day too? This depends on where you live, I think.

regards Astrid

 

[strongm]

But you aren't! FindersKeepers and myself have cunningly obfuscated the original question to the point where you think that the original question involved a Monday. It doesn't...

Mind you, knowing the first working Monday of a month makes it pretty easy to determine whether any other date in that same month is the first working day or not.

Assuming that you have my original FirstMonday function from above, you can add:
[tt]
Private Function IsFirstWorkingDay(dteDate As Date) As Boolean
Dim WeekendCheck
WeekendCheck = DateDiff(&quot;d&quot;, dteDate, FirstMonday(dteDate))
If WeekendCheck >= 0 And WeekendCheck <> 1 And WeekendCheck <> 2 Then
IsFirstWorkingDay = True
End If
End Function
[/tt]
Mind you, MichaelRed's method of just stepping through the days is probably better...

 

[WilMead]

Thanks for the point back to the original question. That does need loop, and some IfThenElese

To determine first working day you need a function to return whether a date is a working day. With that the solution is easy.

Today is the first working day this month if the previous working day was not in this month.

Start By assuming that Today is not a working day.
[tt]
IsFirstWorkingDay = False
if IsWorkingDay(today) then ' Assumption may wrong
IsFirstWorkingDay = True ' Test it
i = today
do
i = i - 1

if Month(i) <> Month(today) Exit do ' Eureka

if IsWorkingDay(i) then ' was right to begin with
IsFirstWorkingDay = False
Exit do
end if
loop
end if
[/tt]

Of course as the thread has pointed out, the function to determine that a date is a working day is the real work.



Wil Mead
wmead@optonline.net

 

[MichaelRed]

I think this is where I came in, so I'm just goint to go out!


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[strongm]

Sawatzky,

I don't think any of the contributors to this thread would argue that their algorithms deal with vacations.
Most, if not all, can fairly easily be modified to deal with them. MichaelRed's is probably the easiest to do so with. You'd just need an array of some sort containing vacations, and extend his loop; the reason it makes the most sense with his algorithm is that it is merely an extension of the underlying logic, whilst the other algorithms would require a special case.

MichaelRed,
Coward...

 

[MichaelRed]

to code again ... another day
MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WilMead]

Strongm,

You asked for it... ;-)

The solution leaving the determination of date as a working day to an undefined but named function takes into account vacations. It takes into account any event rendering a date non-working, even a varying work week. Also it better solves the problem because it externalizes the volitile. In other words, it doesn't change at all because you redefine what a working day is.

Mike's solution efficiently finds the first working day of the month, but... as you pointed out, we want the algorithm for isFirstWorkingDay(ThisDate) not getFirstWorkingDay(ThisMonth).

Sorry, couldn't resist the invitation.




Wil Mead
wmead@optonline.net

 

[MichaelRed]

Hmmmmmmmmmmmmmm,

I hesitate to add to the (already tortured) confusion. I would agree that my procedure does NOT cover 'holidays'. Beyond that, COGITO, it does ANSWER the ORIGINAL question

&quot; ... whether or not the current date is the first business day of the month? ... &quot;

I see no requirement/expectation to cover &quot; &quot; ... account vacations. It takes into account any event rendering a date non-working, even a varying work week ... &quot;&quot;


The inclusion of &quot;vacations&quot; is particularly troubling, as it appears to imply a LOT of source data and / or call arguments.

Worst of all, Will says &quot; ... You asked for it ... &quot; but only teases us with the statememnt, not the actual code (PROOF)!


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over