holidays and weekends, exempt

[jackal63]

My boss was adamant that there is a simple, predefined way in Access to take a range of days and see how many of those days were workdays, and how many were weekends and holidays. Something like isWorkDay(#12/25/2002#)=False. Has anybody heard anything about this functionality, I guessed that you would just have to write a custom work-around for any report that required this differentiation. I'd be glad if I was wrong, though! I found something about a worksheet function that sounded relevant, but I couldn't use any worksheet function from a query, and I had the .dll file Access said I would need to run them.

Any help would be appreciated. Thanks

 

[THoey]

There is no Access work around, but there is a FAQ in the Access General forum that has all the code you need. Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[ETID]

If you link an Excel spreadsheet to your access query or table ...

then you can use excels networkdays function to weed out holdays and weekends


the networkdays function will require a little bit of set up to define the holidays that your organization observes.
(the help explains it fairly clearly)

 

[ETID]

Another way, if you have to stay in access is to make a table listing your holidays. then combine that lookup with the weekday function in access.

the weekday function returns the day of the week for a given date as a value of 1 through 7...so then you can filter by 2 through 6 to get all mon through fri

or filter out 1 and 7

 

[SgtJarrow]

This functionality is NOT predefined in Access, but there are several ways to work it....I have all the code necessary to remove weekends and holidays from a date calculation....Just mail me and I'll send it...

Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@ssmb.com

 

[jackal63]

Well, thanks for the confirmation. I was pretty sure there was some crazy MS automated solution. Anyway, I've already concocted an evil Access workaround involving an "excluded_dates" table and weekend filtering by code.

Thanks again.

 

[MichaelRed]

compare your procedure to faq181-261. MichaelRed
m.red@att.net

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