Calculating Business Days.

[Kraeg]

I'm developing a function that takes a given date, adds 7 days to it, then checks what the resulting day is; if it is a Sat, Sun, or Public Holiday, then it adds one, and checks again. It'll keep checking until the result is a Mon to Fri that isn't a Public Holiday.

The Sat/Sun check is easy enough, and the Public Holidays I will put into my code. What I was wondering is if anyone has done this sort of thing and how did you go about it? I have started coding it, but am interested in other's methods.

I have also thought about accessing an Outlook file folder with Holidays (although I am guessing what PH details are in there depend on what Microsoft put in). I may end up creating a data file with Public Holidays in it that the user can edit; my function will then check that file to see if a resulting date is a PH or not.


I just went into Outlook to check the Holidays and it isn't showing any of them (Australian) despite them being switched on..... something else I'll have to work on.

 

[Kraeg]

I meant 5 days, not 7. If it was adding 7 days it wouldn't need to check for a weekend, because no-one works on a weekend. That just leaves Public Holidays; however, I'll still be checking for a weekend if it results in a PH (since if it finds it is a PH it will add one, and if the PH is on a Friday that will result in a Sat).

 

[rorubin]

Take a look:

http://www.tagconsulting.com/Show.asp?Id=1007

 

[MichaelRed]

faq181-261 is related, and may provide you with some insight

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[vkersey]

I'm working on something similar myself. Here's something I found:

http://support.microsoft.com/default.aspx?scid=kb;en-us;290152

 

[TomKane]

There is a bank payment system called SWIFT anfd they have a currency holidays table for every currency in the world - what they have done is created a table which is indexed by Currency and Date and all that is in it are public holidays and weekends - the idea is that unless there is a record in the table for the date/currency you specify than you can take it that it is a working day.

 

[jel]

Just for fun: you can actually calculate christian holidays related to easter (whitsun, ascencion day). It involves calculating easter-sunday = first sunday after first full moon after march 21.
Sorry, but I could only find a link in Dutch (they love holidays, Christian or any other)

http://www.femda.nl/vragen/antwoord.asp?CD=0&ID=vp0960783527

 

[MichaelRed]

see faq705-3213 posted TODAY!

(Be the first on your block to review this FABOULOUS work!!!) MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Kraeg]

Thanks for these replies everyone.

I found the process for determining which dates Easter falls on for a given year and will be putting them in.

 

[MichaelRed]

Unless you have a consistient approach for determining the date for ALL of the holidays your organization observes, and they are all observed on the official date, reoutines to "calculate" the dates are not all that useful. Many (more likely all) organizations observe at least some holidays on dates other than the official date, thus requiring at least some reasonable method of having the observed date available. Further, in some locales different businesses (or industries) observe different holidays, reenforcing the need to customize the list of holiday dates. I have implemented a few versions of the date forecast and even more variations on the workdays between two dates. In no instance was I able to have just a set of procedures which return the observed dates for the holidays. In order to maintain the holiday date listing, a simple table of holidays and their observed date serves as a convenient resource to simply check the dates. A simple form to maintain the table with the standard add/edit/delete functions provides an admin the necessary interface.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places