Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    code for Holiday or Vacation? (any)

    This <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=25289&page=&v iew=&sb=&o=&vc=1#Post25289>post</A> has links to sites that let you look up holidays in any country. Good for planning a vacation. (The link to Chip Pearson's site appears broken).

    I need a means in VB/VBA of looking up a table of holidays. It's a billing application (cheap rates after 6pm and on weekends and holidays).

    So far I'm thinking of using ye olde hacke known as INI files, because my user can edit the file as time goes on (or I can dash off a quick GUI).

    It seems that a more generally useful chunk of utility code would be one that used a look up file/table with dates and countries, so that one could offer a country name or code and a date in standard format, and receive at least a boolean value indicating vacation status for that date.

    Nice-to-have features would include the ability to scan to the previous/next vacation day, exclude or include weekends, and so on.

    Is anyone aware of such a beast? For the time being I can use an INI file and hand-carve the dates for Canada, using Excel cell replication for the weekends and two-fingered typing for public holidays the next two years.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for Holiday or Vacation? (any)

    Don't know if this is what you want but, for what it's worth. I did a macro in Word97 that I use to calculate a date in the future (DIF), which in itself is not difficult. However, that macro detects if that DIF falls on a US holiday or weekend. If it does, it looks for the next workday (Monday through Friday). For holidays falling on Saturdays or Sundays, it assumes that the holiday is celebrated either the previous Friday or next Monday, and adjust accordingly. It could be the basis for a more complex setup based on holidays from different countries. Let me know.

  4. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: code for Holiday or Vacation? (any)

    Outlook has a file similar to an INI design that lists the holidays. Use your favorite Lounge function (Search) over in the Outlook forum for the holidays file name and location.

  5. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for Holiday or Vacation? (any)

    > if that DIF falls on a US holiday

    This is the salient point. I can calculate a date, and can work out weekends. I'm wondering how you defined the "Public Holidays" (in your case for the US).

    Did you enter a small data file, of some type, that would need to be updated each year, or did you encode an algorithm for public holidays (such as "In our country, Thanksgiving is the first Monday of the month of October", or like the calculation for Easter based on the Golden Number.

    I'm vacillating between writing complex code that the client does not have to maintain, and writing simple code that accesses a data file that must be maintained by the user.

  6. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for Holiday or Vacation? (any)

    > your favorite Lounge function (Search) over in the Outlook forum

    OK. I'm dense.


    I couldn't find a reference. If you have a sample file, might you be able to paste a few lines here? As you will have seen by my other reply, I'm trying to decide whether to go the data file route or the algorithm route.

  7. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for Holiday or Vacation? (any)

    Chris: I'm attaching the code I wrote. Please keep in mind that I am new to this. Be compassionate, OK!? Hope it helps.
    Attached Files Attached Files

  8. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for Holiday or Vacation? (any)

    >Be compassionate,

    "pitying", from an obsolete French Word, but why should I feel pity? If you are "new to this", I say "Right On!", or at least, I would if I were Canadian. The English in me says "Welcome, old boy!". The Aussie in me says "Grab a tinny", and the Lounger in me mutters to myself "Fresh Meat!". Hah hah!


    OK. Back to business. I'm still studying your excellent VBA code (there now, don't you feel better?", and have a question surrounding the chunk below.

    I DIMmed the two variables because I always operate with Option Explicit.

    From what do I obtain the reference to "Assistant."? I could see nothing that jumped out at me when I examines Tools, References in VBA.




    My latest thinking is that I need some sort of parameter file that indicates fixed date holidays (such as Dec25) and variable date holidays as some form of function, or as a reference to a small built-in function within the program. I'm sure that someone has done this already, or is doing it right now, unbeknownst to me.



    <pre>' Create the Assistant that will let the user select the type of date desired
    Dim b, x
    Set b = Assistant.NewBalloon
    With b
    </pre>


  9. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code for Holiday or Vacation? (any)

    Chris,

    As you probably know Excel has some functions for dealing with workdays etc, and if you have a holiday list it can be taken into account. However as you are not using Excel (I assume) I will nevertheless point you at <A target="_blank" HREF=http://www.cpearson.com/excel/holidays.htm#Easter>Pearson Software Consulting</A>, where you will find find some code snippets that might help you, especially for dealing with Easter.

    Andrew

  10. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for Holiday or Vacation? (any)

    > I will nevertheless point you at Pearson Software Consulting,

    An excellent reference, duly bookmarked. Thank You.

    I was happy to see "fixed and floating" etc. I shall pore over the details to write/adapt generalized holiday-generators for the client.

  11. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for Holiday or Vacation? (any)

    (I recommend <A target="_blank" HREF=http://www.cpearson.com/excel/holidays.htm>Chip Pearson's site</A>, from which I have culled some definitions of holidays)

    Notes on Holidays
    =============

    This is a billing application. Transactions are entered using a date field, and are stored for all time. Any code which deals with holidays must retain the ability to decode holidays for all times, since we will want to run historical reports, reporting holidays in the current year will not suffice.

    Adopt an 8-character-digit date format as standard.

    A procedure which functions in unattended mode must be able to determine holidays as time goes by, and to remember them. The latter suggests that holidays, once calculated, are stored in a reference table or file. We might elect to use a database table or INI files, for reasons set out below. The former suggests that the code ought to be able to determine the status of any date and assign it to the stored data.

    Telephone and similar reduced-rate billing applications typically have rates for Saturdays, Sundays and Public Holidays. We elect to assign flag-values of "1", "2" and '3" respectively. That is, we signal a date as being a holiday if the flag-value is non-zero. Our stored data ought to include a descriptive commentary field that will permit us to annotate our findings, so that an INI file entry might appear as:

    20011225=3;Christmas Day

    We can extend the classification as far as we like, for example class4 might be for notification (Daylight Saving April 1st!")

    Our application ought provide a human-user interface to permit the editing of and checking of holidays. At the very least we should provide a printout of our understanding of holidays for the coming year, a calendar of holidays, which client staff can check before the billing application is run.

    If the billing application includes a front-end, then we might store the holiday status (1, 2 or 3) in the transaction record.

    We need a mechanism for defining holidays.
    Type Date Geo Determination Class
    Function Easter Day 3
    Function Good Friday 3
    Saturday 20010106 Saturday 1
    Sunday 20010107 Sunday 2
    Fixed Yyyy0101 New Year's Day 3
    Fixed Yyyy0214 Valentine's day 4
    First Sunday Yyyy04 Daylight Saving 4
    Last Monday Yyyy05 USA Memorial Day 3
    4th Thursday Yyyy11 USA Thanksgiving Day 3
    Fixed Yyyy1225 Christmas Day 3
    We ought also to consider a geographical source code that will allow us to assign local holidays. For example, June 24 2001 was St. Jean-Baptiste day in the province of Quebec Canada, but might not be considered as a public holiday in Alberta. What of "Toronto day" in the city of Toronto? This date will be of sigificance to transactions effected in Toronto (Postal code "M?? ???") but not in the rest of Ontario (Province = ON, Country = CA).

    The geographic code in the holiday table should lend itself to upward lookup so that a city code can be translated into a state and then a country code to check holidays at all levels of government.

    We ought also to consider days which might be of significance to the client demographics such as 9th September 2001 (Grandparents day) or 30th September 2001 (National Police and Peace Officers day).

    These considerations lend weight to the consideration of the final arbitrer being a lookup file or table which can be edited by the client.

    The transaction record (for a telephone billling or payroll application) will thus include a start date/time and either an end date/time or a duration, a geographic origin (for holiday assignment) and a holiday tag.

    Since telephone billing applications often include "peak" and "shoulder" times of day, we need also figure this into calculations.

    Adopt an 8-character-digit time format as standard.

    The client will define whether an event which straddles a time band will take the entry time, the exit time, or a group of times as the rate calculator/

    For example, suppose that calls before 8pm are peak, between 8pm and 11pm are shoulder, and 8pm to 8am are basic. How do we calculate for a call which begins at 7:30pm (in peak time) and lasts until 11:30pm (in basic time)? Is it a four-hour call at peak rates, a four-hour call at basic rates, or is it three calls of

  12. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for Holiday or Vacation? (any)

    I should have told you that the code was a macro made in Word97. I was using the assistant there. Thought that the "logic" used to identify holidays might help you. Hope it does. Take care!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •