Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Apr 2008
    Location
    Edmonton, Alberta, Canada
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm using the Handling Weekends and Holidays in Calculated Dates and the Following Business Day subsection.

    I want to include Victoria and Canada Days in my holiday calculations, but their dates cannot be defined by the methods you have used in other parts of the document. Victoria Day is officially May 24 and Canada Day is officially July 1, but the date is changed to provide a long weekend for the masses. In the case of Victoria Day, the date is switched to be on the Monday closest to the 24th so that it may be either the Monday of the 3rd or 4th week in May, depending on which day of the week the month starts (e.g.: May 18, 2009 or May 23, 2011). In the case of Canada Day, the date is switched to July 2 if July 1 is on Sunday. Some employers give their employees a holiday on the Friday after July 1, and ask them to work on July 1 instead, thereby providing them a long weekend.

    I hope this doesn't involve too much work.

    TIA
    David

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='COMPASSGUY' post='792368' date='07-Sep-2009 09:24']I'm using the Handling Weekends and Holidays in Calculated Dates and the Following Business Day subsection.

    I want to include Victoria and Canada Days in my holiday calculations, but their dates cannot be defined by the methods you have used in other parts of the document. Victoria Day is officially May 24 and Canada Day is officially July 1, but the date is changed to provide a long weekend for the masses. In the case of Victoria Day, the date is switched to be on the Monday closest to the 24th so that it may be either the Monday of the 3rd or 4th week in May, depending on which day of the week the month starts (e.g.: May 18, 2009 or May 23, 2011). In the case of Canada Day, the date is switched to July 2 if July 1 is on Sunday. Some employers give their employees a holiday on the Friday after July 1, and ask them to work on July 1 instead, thereby providing them a long weekend.

    I hope this doesn't involve too much work.

    TIA
    David[/quote]
    Hi David,

    Regarding the Canada Day holiday, I believe the field in the tutorial already has the logic to handle a holiday that falls on a Sunday. See, for example, the parts dealing with Australia Day or, perhaps, ANZAC day. Note, though, that the code can't handle some employers doing one thing and others doing another. That's something probably best handled on a case-by-case basis (ie you could use different versions of the code for different employers - you may even have to incorporate some of the code from the 'Previous Business day' example to shift the holiday to the preceding Friday).

    As for Victoria Day, you'd need to build in the necessary logic into the field. Although you say "the date is switched to be on the Monday closest to the 24th", what happens if the 24th is a Thursday - Thursdays day being 3 days BEFORE AND AFTER the nearest Mondays?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Apr 2008
    Location
    Edmonton, Alberta, Canada
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='macropod' post='792380' date='06-Sep-2009 21:27']Hi David,

    Regarding the Canada Day holiday, I believe the field in the tutorial already has the logic to handle a holiday that falls on a Sunday. See, for example, the parts dealing with Australia Day or, perhaps, ANZAC day. Note, though, that the code can't handle some employers doing one thing and others doing another. That's something probably best handled on a case-by-case basis (ie you could use different versions of the code for different employers - you may even have to incorporate some of the code from the 'Previous Business day' example to shift the holiday to the preceding Friday).

    As for Victoria Day, you'd need to build in the necessary logic into the field. Although you say "the date is switched to be on the Monday closest to the 24th", what happens if the 24th is a Thursday - Thursdays day being 3 days BEFORE AND AFTER the nearest Mondays?[/quote]

    Thanks for the quick reply, macropod.

    I'll take a look at the coding for Australia Day for dealing with a Sunday holiday.

    In looking up a website that provides dates of holidays in years to come, it turns out that in 2012, May 24 falls on a Thursday, and Victoria Day is on the previous Monday, May 21. However, the bigger issue is figuring out the logic to test for that condition. My brain is about to explode trying to figure out how you do what you do with your fields. It's amazing. I'll give it a shot, but do you mind if I post back here if I'm stuck? Which brings to mind another question. How do you test to see if the fields that you have written do their intended job? Do you change the computer date, and then update the fields?

    I want to remove the references to AusDay and ANZAC. When I modify the fields that follow the comment "{SET % Now that we've got all the holiday dates,...}" do I only remove the AusDay and ANZAC fields, or do I also have to make any changes to the fields with the MOD functions in them. Unfortunately, I don't fully understand the logic of those fields, so I have to ask the question, rather than just doing it myself.

    Thanks for your help.
    David

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='COMPASSGUY' post='792387' date='07-Sep-2009 14:20']Thanks for the quick reply, macropod.

    I'll take a look at the coding for Australia Day for dealing with a Sunday holiday.

    In looking up a website that provides dates of holidays in years to come, it turns out that in 2012, May 24 falls on a Thursday, and Victoria Day is on the previous Monday, May 21. However, the bigger issue is figuring out the logic to test for that condition. My brain is about to explode trying to figure out how you do what you do with your fields. It's amazing. I'll give it a shot, but do you mind if I post back here if I'm stuck? Which brings to mind another question. How do you test to see if the fields that you have written do their intended job? Do you change the computer date, and then update the fields?

    I want to remove the references to AusDay and ANZAC. When I modify the fields that follow the comment "{SET % Now that we've got all the holiday dates,...}" do I only remove the AusDay and ANZAC fields, or do I also have to make any changes to the fields with the MOD functions in them. Unfortunately, I don't fully understand the logic of those fields, so I have to ask the question, rather than just doing it myself.

    Thanks for your help.
    David[/quote]
    Hi David,

    In addition to deleting the SET fields that establish the AusDay and ANZAC variables, plus any others you're not using, you'll need to delete the corresponding mathematical expressions (eg +(AusDay-1=jd)+(ANZAC-1=jd)) and substitute the variables a corresponding mathematical expressions for Victoria Day and Canada Day.

    I would expect there to be legislation setting out when the official publich holidays for Victoria Day and Canada Day should be observed, both as to the official date and to what should happen when that date doesn't fall on, say, a Monday or Friday. That should be your starting point for inserting the necessary logic into the code.

    Of course, you're working with perhaps the most complex field coding in the tutorial, which means you're working at the deep end and the learning curve will be steep - it may even have cornices! The logic in this field is essentially a compilation from what I've used in numerous simpler fields throughout the document, and you might find it wortwhile taking the time to understand the process used earlier in the tutorial to turn dates into numerical values and back again, plus how the fields to 'Calculate the day & date of a given day of the week next month’ and ‘Calculate Dates Of Easter’ work.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    FWIW

    Victoria Day always falls on the last Monday preceding 25 May.

    Canada Day always falls on 1 July unless it falls on a Sunday. The Holidays Act provides that July 2 is Canada Day when July 1 is a Sunday.
    Regards
    Don

  6. #6
    New Lounger
    Join Date
    Apr 2008
    Location
    Edmonton, Alberta, Canada
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='macropod' post='792392' date='07-Sep-2009 02:36']Hi David,

    In addition to deleting the SET fields that establish the AusDay and ANZAC variables, plus any others you're not using, you'll need to delete the corresponding mathematical expressions (eg +(AusDay-1=jd)+(ANZAC-1=jd)) and substitute the variables a corresponding mathematical expressions for Victoria Day and Canada Day.

    I would expect there to be legislation setting out when the official publich holidays for Victoria Day and Canada Day should be observed, both as to the official date and to what should happen when that date doesn't fall on, say, a Monday or Friday. That should be your starting point for inserting the necessary logic into the code.

    Of course, you're working with perhaps the most complex field coding in the tutorial, which means you're working at the deep end and the learning curve will be steep - it may even have cornices! The logic in this field is essentially a compilation from what I've used in numerous simpler fields throughout the document, and you might find it wortwhile taking the time to understand the process used earlier in the tutorial to turn dates into numerical values and back again, plus how the fields to 'Calculate the day & date of a given day of the week next month’ and ‘Calculate Dates Of Easter’ work.[/quote]

    Hi Macropod

    I'm making some headway on the project, slowly wrapping my brain around your logic, however I have run into a snag.

    The field {SET yr{=100*e+g-4800+INT(i/10}} references the variable 'i', which at this point, hasn't been defined as yet. I assume that a field is missing.

    The method I am working on for Victoria Day is as follows:
    When May 1 falls on either the Sat, Sun or Monday, Victoria Day occurs on Monday of the 4th week. When May 1 falls on any other day of the week, Victoria Day occurs on Monday of the 3rd week. I'm using that information to determine the date using the same method that you used to calculate Canberra Day Holiday, assigning the 'week' variable to a calculated value. Your comments are welcomed.

    TIA
    David

  7. #7
    New Lounger
    Join Date
    Apr 2008
    Location
    Edmonton, Alberta, Canada
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='COMPASSGUY' post='792485' date='08-Sep-2009 02:44']Hi Macropod

    I'm making some headway on the project, slowly wrapping my brain around your logic, however I have run into a snag.

    The field {SET yr{=100*e+g-4800+INT(i/10}} references the variable 'i', which at this point, hasn't been defined as yet. I assume that a field is missing.

    The method I am working on for Victoria Day is as follows:
    When May 1 falls on either the Sat, Sun or Monday, Victoria Day occurs on Monday of the 4th week. When May 1 falls on any other day of the week, Victoria Day occurs on Monday of the 3rd week. I'm using that information to determine the date using the same method that you used to calculate Canberra Day Holiday, assigning the 'week' variable to a calculated value. Your comments are welcomed.

    TIA
    David[/quote]

    Hi Macropod

    I inserted the {SET h......} and {SET i......} fields and it seems to work as it should. I trust that's all that is needed.

    David

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi David,

    I'm glad you picked up those errors in the field code. It seems a couple of lines went AWOL somewhere along the way. Their absence doesn't have any effect in the tutorial doc, though, as fields used elsewhere were conveniently setting those variables to the correct values for those fields. I'll have to update the tutorial.

    With Victoria Day, the approaches you could take include:
    1. calculate the 19th of May and, if that's not a Monday, add however many days are needed to get the next Monday.
    2. calculate the 25th, then subtract however many days are needed to get the preceding Monday.
    3. calculate the 3rd Monday and, if that's less than the 19th, add 7 days.
    4. calculate the 4th Monday and, if that's more than the 25th, subtract 7 days.
    Your approach seems to be based on (3), which should be eminently suitable.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    New Lounger
    Join Date
    Apr 2008
    Location
    Edmonton, Alberta, Canada
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='macropod' post='792489' date='08-Sep-2009 03:59']Hi David,

    I'm glad you picked up those errors in the field code. It seems a couple of lines went AWOL somewhere along the way. Their absence doesn't have any effect in the tutorial doc, though, as fields used elsewhere were conveniently setting those variables to the correct values for those fields. I'll have to update the tutorial.

    With Victoria Day, the approaches you could take include:
    1. calculate the 19th of May and, if that's not a Monday, add however many days are needed to get the next Monday.
    2. calculate the 25th, then subtract however many days are needed to get the preceding Monday.
    3. calculate the 3rd Monday and, if that's less than the 19th, add 7 days.
    4. calculate the 4th Monday and, if that's more than the 25th, subtract 7 days.
    Your approach seems to be based on (3), which should be eminently suitable.[/quote]

    Hi macropod

    I actually stumbled on a very simple solution:

    {SET VicDay{=144+LpYr+yjd}
    {SET VicDay{={VicDay}-(MOD(VicDay,7))}

    having discovered that the number of days past the target Monday that May 24 actually is, is the same as the day number of the week, hence the (MOD(VicDay,7))

    Thanks for your thoughful input.
    David

Posting Permissions

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