Announcement

Collapse
No announcement yet.

UK Bank Holiday Calculator

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • UK Bank Holiday Calculator



    I found a link to a website on one of the forum pages. I had a look in the website and it showed a formula for calculating when Easter falls - I didn't know it could be worked out, but it can!!

    I therefore decided to investigate further. I picked up another formula to calculate the first MOnday in May and I have now put together a little spreadsheet that will calculate all bank holidays in the year entered in cell B1.

    It also takes into account additional bank holidays that exist when Christmas Day and/or Boxing Day fall on a weekend.

    Not sure how many people will find it useful, but I certainly do!
    Attached Files

  • #2
    Re: UK Bank Holiday Calculator

    Check Chip Pearson's site for some good stuff related to this. I will have a look at your calculator later Richard
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: UK Bank Holiday Calculator

      Richard

      I've just got round to looking at this. You might want to amend your Formula to allow for New Year's Day falling on a Sunday. If this happens the Bank Holiday moves to Monday I think

      =IF(WEEKDAY(DATE(B1,1,1),2)<7,DATE(B1,1,1),DATE(B1,1,2))

      Likewise for Xmas Day & Boxing Day
      Last edited by royUK; May 18th, 2006, 04:37.
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      For free Excel tools & articles visit my web site

      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

      RoyUK's Web Site

      royUK's Database Form

      Where to paste code from the Forum

      About me.

      Comment


      • #4
        Re: UK Bank Holiday Calculator

        Thanks for that - I had remembered re Christmas and Boxing Day (still had 25th and 26th of Dec, but had 'additional' days if applicable). Never thought of New Year's Day!

        Comment


        • #5
          Re: UK Bank Holiday Calculator

          I know this is an OLD thread, but that little sheet saved me a lot of trouble! So Ta!

          Incidently, I think you need to take account of BH's falling on a Saturday or a Sunday - not just Sunday as the royUK's does.

          So to move new years day holiday from sat or sun to a mon, the formula would have to look something like:

          Code:
          =IF(WEEKDAY(DATE(B1,1,1),2)<6,DATE(B1,1,1),IF(WEEKDAY(DATE(B1,1,1),2)=6,DATE(B1,1,3),DATE(B1,1,2)))
          Not sure if that's the easiest way to do it, but I'm a simpleton and can just about get my head around it.

          Would probably be easier to have a little VBA function to check whether any given date is a weekend, and move that date to the next Monday...?

          Comment


          • #6
            Re: UK Bank Holiday Calculator

            ee ba gum - how about this VBA (plaguarised and patched together from the nets and trial and error - I know squat about VBA!):

            Code:
            Public Function HolidayWeekend(InputDate As Date) As Date   'determine if a given date falls on a saturday
                Select Case Weekday(InputDate)                          'or sunday. If it does, return the following monday
                    Case vbSaturday
                        HolidayWeekend = InputDate + 2
                    Case vbSunday
                        HolidayWeekend = InputDate + 1
                    Case Else
                        HolidayWeekend = InputDate
                End Select
            End Function
            Called from a cell by
            =HolidayWeekend (TheDate)
            Where 'TheDate' is the date you want to query (or cell ref of that date). Seems to work - tried new year's day for 2010,11 and 12 which fall on Fri, Sat and Sun respectively.

            Comment


            • #7
              Re: UK Bank Holiday Calculator

              This is just what I have been looking for, and although it's an old thread there is still nothing else like it.
              Is there any chance you could update it to include 1st of January & remove Easter Sat & Sun as they ar not BHs
              Great work thanks


              Mike

              Comment


              • #8


                Re: UK Bank Holiday Calculator

                Welcome to the board, Mike54.

                This thread is over 6 years old, admittedly with some newer comments. The policy here is you do not post questions in threads started by other members.

                Please start a new thread for your question, if you wish, you can include a link to this thread by copying the URL from the address bar of your browser and pasting into your message.

                You might also like to read the page referred to in post #2, however it has moved in the intervening years. The new URL is here

                Comment

                Working...
                X