UK Bank Holiday Calculator

[richardaylin]

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!

[royUK]

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

[royUK]

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

[richardaylin]

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!

[bernmc]

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:

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

[bernmc]

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

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.

[Mike54]

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

[cytop]

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