Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: UK Bank Holiday Calculator

  1. #1
    Join Date
    24th January 2006
    Location
    Loughborough, United Kingdom
    Posts
    103

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,427

    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

    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

    About me.

  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,427

    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 at 04:37.
    Hope that Helps

    Roy

    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

    About me.

  4. #4
    Join Date
    24th January 2006
    Location
    Loughborough, United Kingdom
    Posts
    103

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    3rd November 2010
    Posts
    2

    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:

    VB:
    =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...?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    3rd November 2010
    Posts
    2

    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!):

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    30th November 2012
    Posts
    1

    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    1st September 2010
    Posts
    7,334

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Insert Bank Rows After Each Occurences
    By kenh32 in forum EXCEL HELP
    Replies: 2
    Last Post: September 22nd, 2007, 11:37
  2. Holiday Pay Calculator
    By kevinm12 in forum EXCEL HELP
    Replies: 2
    Last Post: May 21st, 2007, 20:54
  3. Creation Of An Exam Bank
    By djmiles in forum Excel and/or Word Help
    Replies: 2
    Last Post: March 16th, 2007, 04:18
  4. Bank Interest Calculator
    By VashApollo in forum EXCEL HELP
    Replies: 4
    Last Post: December 1st, 2006, 04:29

Bookmarks

Posting Permissions

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