How To Calculate UK Bank Holidays

[sherlock99]

Hi,

is there any way in excel to calculate the dates when UK bank holidays fall ?. There are websites that will show you the exact dates I'm after, many years into the future which suggests it can be calculated:

2016 holidays
2016 Public Holidays. 2016 Bank Holidays. 2016 Calendar. Dates.

New Year's Day	January 1
Good Friday	March 25
Easter Monday	March 28
Early May Bank Holiday	May 2
Spring Bank Holiday	May 30
Summer Bank Holiday	August 29
Boxing Day	December 26
Christmas Day Holiday	December 27

<tbody>
</tbody>

2055 holidays
2055 Public Holidays. 2055 Bank Holidays. 2055 Calendar. Bank Holiday Dates.

New Year's Day	January 1
Good Friday	April 16
Easter Monday	April 19
Early May Bank Holiday	May 3
Spring Bank Holiday	May 31
Summer Bank Holiday	August 30
Christmas Day Holiday	December 27
Boxing Day Holiday	December 28

<tbody>
</tbody>

I need to be able to replicate this in a timesheet to future proof it when a field with the financial year changes. I've had a look around the forums, some which offer ways to count weekends/bank holdays but none that display the dates. Naturally it has to account for bank holidays falling on weekends

Any help would be approciated.

 

[etaf]

Christmas and boxing day is 25th or 26th Dec
However, if one falls on a weekend , then you get the monday / tuesday off
Summer bank holiday - is the last Monday in August
Spring bankholiday s the last monday in May
Early May bank holiday is first monday in May

Easter Friday/Monday - Thats calculated based on moon phases
How the Easter Date is Determined


New years day is 1st jan - unless a weekend and then the following Monday

 

[sherlock99]

Thanks for that etaf,

so what you are saying is that all bank holidays with the exception of Easter are straight forward enough to calculate based on the date it falls and if its a weekend day. Are there any suggestions of the cell equation that can calculate Good Friday, say based on cell A1 having a year value in it?. Until I saw the above website achieving it, I wasn't aware it could be mathematically predicted.

Many thanks.

 

[sherlock99]

Scrub that follow up question etaf.

A little more searching around the forum and I've found a post that answers the question.

http://www.mrexcel.com/forum/excel-questions/389703-how-calculate-date-easter.html

Thanks again for your time.

 

[etaf]

nice find , and thanks for sharing here , for others who may need the same solution

 

[sherlock99]

My pleasure.

I thought I should add one extra link that might help. The previous MrExcel link works fine for workbooks configured to use the 1900 date system. If, like me you also need an Easter calculating solution when working with the 1904 date system (for negative time calculations) then I recommend this link:

http://www.contextures.com/exceleastercalculation.html

I opted to use the Function EASTER_by_NHarker, which worked perfectly.

 

[luca91]

Bank Holidays UK 2017

New Year's Day 2017 Sunday, January 1, 2017
Good Friday 2017 Friday, April 14, 2017
Easter Monday 2017 Monday, April 17, 2017
Early May bank holiday 2017 Monday, May 1, 2017
Spring bank holiday 2017 Monday, May 29, 2017
Summer bank holiday 2017 Monday, August 28, 2017
Christmas Day 2017 Monday, December 25, 2017
Boxing Day 2017 Tuesday, December 26, 2017