Holiday Calendar Up To 2500 ( The Fast Way )

  • Often it is necessary to reserve one or more columns to holiday lists, and afterward one has to maintain them and keep them up-to-date or delete the past ones. When this has to be done on more than one spreadsheet can became a pretty unpleasant task.

    Here is a fast way to get the official holidays calculated from now ( actually from 1900 ) to 2500, including managing week-ends.

    The concept is pretty easy and works for any country even if with some changes :

    FORGET the year for the moment. Holiday fall on a specific calendar day ( Jan. 1st, Dec. 25th ) or on a specific day of the week ( President and Martin L King's day, etc. ); and we have to make the whole more tasteful some exception.

    Let's take for example:
    - Jan. 1: we can see it as 1 ( Jan. ) * 100 + 01 = 101;
    - Dec. 25: we can see it as 12 ( Dec. ) * 100 + 25 = 1225.

    indipendently from the year we can find out immediately if today is holiday or not; exception if 1225 is saturday or sunday ), etc.

    now consider Martin L King or Easter day:
    - MLK falls the third Monday in January but alway between Jan. 15 and Jan 22; that means
    between 115 and 122;
    - Easter ( here you need the year ) falls always on a Sunday between Mar. 3rd ( 322 ) and Apr. 25 ( 425 ) - Why? please refer to Wikipedia.

    The only "complex" part is the calculation of Easter ( I use the Gaussian formula ), afterward is just a question of checking if conditions are met.

    The code I propose is written for my own pourpose ( I need the Financial Market - not stock exchange - good business days ), for EUR and USD; anyone can easily adapt it to his own need.


  • Re: Holiday Calendar Up To 2500 ( The Fast Way )

    Thank you for allowing me to ask you a question filippo,

    I am just a beginner at VBA and I have looked at your code above and it is quite an impressive algorithm (I can understand the Select Case statement in what you are doing with the number ranges). I just want to ask you if you can help me with my problem as follows:

    I am in charge of date stamping a huge number of claim forms every day Monday - Friday (minus holidays\official days off). The claim forms come to my desk already printed out and what I do is take the huge stack, place it in the printer tray and run an MS Word template to print a date stamp over all of them. I have to use transparent text in a text box so that my date stamp won't obscure any of the preprinted claim characters. The way it works is the mail room receives the mail 1 day I receive it on my desk except for the Friday mail room receipt in which I receive it on Monday (of course, there are variations of this routine due to holidays\official days off).

    What I am trying to create and need help with is an AutoOpen macro that will automatically run when I open the MS Word file that will populate 2 text boxes with the following: "NMM RECEIVED: [date mail room received the claim forms in a format of Monday, July 8, 2013, for example]". I hope I can attach my MS Word file to show you the VBA macro syntax I am currently using, but if I can't I will just post my code in the reply window.

    I plan to play around with your code but you created it in MS Excel which I guess is a little different than MS Word (I tried to see if I can run the code but it didn't show up in the list of macros for me). I am thinking that this sort of problem I am having is really easy for you to provide the VBA coding syntax that will work for me (along with a little push and pull to help me understand what the code is doing and to answer a few of my questions).

    If you can help me with this, I would be really appreciative and it would help me learn VBA more and more.

    For the file I attached, you are going to have to change the file extension to .doc or .docx I believe.....

    Thank you in advance

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!