First Working Day in the Month

I need a slick way of determining if a given day is the first working day of a given month and if it is, take a set of actions. The only way that I can think of accomplishing this is to store the date once the condition has been met and then check each day to see if the action has already taken place during that month.

This code needs to be part of a set of End-Of-Day routines that are manually triggered.

I thought also about building a table with the first working day in each month. However, if inclement weather were to cause office closure, the set of actions for the month would never take place.

Any slick ideas as my approach seems a little kludgy.

Thanks for the help.
LVL 3
dwcummingsAsked:
Who is Participating?
 
PatHartmanCommented:
If the first day of the month is Saturday or Sunday, you can add 2 or 1 to get to the first working day.  But, you also need to keep a table of holidays and adjust the first day yet again if it falls on a holiday.

If what you really want to do is to run something ONCE, the first time someone opens an app in the month, then simply keep a log table.  Log the year and month or full date if you want when the process runs.  Then each time the app opens, check the log.  If the process has already run for the month, open as normal.  If not, run it, log it, then continue.

This method avoids the problems of unexpected closures or forgetting to maintain the holiday table.

I've attached a sample database with some useful date functions.
UsefulDateFunctions120212Call.zip
0
 
dwcummingsAuthor Commented:
Excellent. Thanks for the date functions database. I will certainly make good use of them in the future.
0
 
aikimarkCommented:
This seems to be a slick calculation:
somedate = #3/15/2015#
firstofmonth = dateserial(year(somedate), month(somedate), 1)
firstworkday = firstofmonth + ((vbmonday-weekday(firstofmonth) mod 7) * -(weekday(firstofmonth, vbMonday) > 5))

Open in new window

Then you only need to compare your somedate variable to the firstworkday variable to determine whether you do your action:
Example:
If somedate = firstworkday Then
    'Do some first-of-month actions here
End If

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.