UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Calculate first business day of the month    
 
   
jhawklentz
post Feb 13 2006, 12:14 PM
Post#1



Posts: 12
Joined: 28-November 05
From: Kansas


How can I calculate the first business day of every month?
Go to the top of the page
 
iandouglas
post Feb 13 2006, 12:22 PM
Post#2



Posts: 1,072
Joined: 22-October 03
From: Manchester UK


The WeekDay function applied to a date returns 1 if the date is a Sunday thro' to 7 if its a Saturday. You could apply this function to calculate the first day of the month that would fall within the business week.
This would ignore holidays of course!
Go to the top of the page
 
jhawklentz
post Feb 13 2006, 12:33 PM
Post#3



Posts: 12
Joined: 28-November 05
From: Kansas


I don't understand how this would work...Can you be a little more specific.
Go to the top of the page
 
NoahP
post Feb 13 2006, 02:06 PM
Post#4


Retired Moderator
Posts: 10,493
Joined: 12-January 01
From: Lexington/Louisville KY USA


Do an advanced search for "networkdays". Make sure you indicate over all dates, or at least a few months.
This has been addressed many times.
Go to the top of the page
 
schroep
post Feb 13 2006, 03:05 PM
Post#5


UtterAccess VIP
Posts: 5,202
Joined: 21-July 05
From: Denver, Colorado [USA]


DateSerial(Year([date]), Month([date]), 1) + Choose(Weekday(DateSerial(Year([date]), Month([date]), 1), vbMonday), 0, 0, 0, 0, 0, 2, 1)
..will give you the first business day of the month specified in [date].
This does NOT take holidays into account; you'll need a custom function to do that. Search the archives here at UA for functions to do just that.
Go to the top of the page
 
Larry Larsen
post Feb 13 2006, 03:31 PM
Post#6


UA Editor + Utterly Certified
Posts: 24,350
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Oalso had ago with a different approach using a custom function which you pass the your date through:
CODE
Public Function fFirstBusinessDay(Optional InputDate As Variant) As Date
Dim I As Integer
Dim dteTemp As Date
Dim strVal As Integer
If IsMissing(InputDate) Or Not IsDate(InputDate) Then
    '/ Set default to current month
    dteTemp = DateSerial(YEAR(Date), Month(Date), 1)
Else
    dteTemp = InputDate
End If
For I = 1 To 7
    strVal = Weekday(dteTemp)
        If strVal >= 2 And Weekday(dteTemp) <= 6 Then
            fFirstBusinessDay = dteTemp
                Exit For
        End If
    dteTemp = dteTemp + 1
Next I
End Function

Date foramt mm/dd/yyyy
Test Resulst:
? fFirstBusinessDay(#01/01/2006#)
02/01/2006
? fFirstBusinessDay(#02/01/2006#)
01/02/2006
? fFirstBusinessDay(#03/01/2006#)
01/03/2006
? fFirstBusinessDay(#04/01/2006#)
03/04/2006
? fFirstBusinessDay(#05/01/2006#)
01/05/2006
? fFirstBusinessDay(#06/01/2006#)
01/06/2006
? fFirstBusinessDay(#07/01/2006#)
03/07/2006
? fFirstBusinessDay(#08/01/2006#)
01/08/2006
? fFirstBusinessDay(#09/01/2006#)
01/09/2006
? fFirstBusinessDay(#10/01/2006#)
02/10/2006
? fFirstBusinessDay(#11/01/2006#)
01/11/2006
? fFirstBusinessDay(#12/01/2006#)
01/12/2006
? fFirstBusinessDay()
01/02/2006
This also does not take into account holiday's but cold be modified.
HTh's
thumbup.gif
Go to the top of the page
 
bcmarshall
post Jun 10 2019, 02:21 PM
Post#7



Posts: 35
Joined: 17-July 10
From: Ventura, CA


schroep, I know this is a very old thread but things persist pretty much forever here and your solution was elegantly simple! I love how it's just an expression, not even a VBA code. One thing, though. When used as an expression, such as Date()=DateSerial(Year(Date()), Month(Date()), 1) + Choose(Weekday(DateSerial(Year(Date()), Month(Date()),1)), 1, 0, 0, 0, 0, 0, 2), I had to remove the reference to vbMonday and change the order of the numbers in the choose section, since 1=Sunday and we'd have to add 1 day to arrive at Monday, and Saturday would add 2 days to arrive at Monday.

Awesome work. Here it is, years later, and it's still helping people.

In the iteration I've chosen, it's simply looking at today's date and seeing whether it matches the first workday of the month. If it does, then I can tell it to perform desired functions, and if not those functions are ignored.

hat_tip.gif thanks.gif thumbup.gif woohoo.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th June 2019 - 06:26 AM