Calculate UK Bank Holidays

[fintail99]

Hi there folks,

A1: 01-Sep-23
A2: =NETWORKDAYS(A1,EOMONTH(A1,0))
A3: I would like to identify the number of UK Bank Holidays in this cell

Cell A1 will always contain the 1st day of the month.
Cell A2 calculates the number of Mon-Fri working days until the end of the month. I use the EOMONTH feature to avoid using a separate cell that comprises a date for the end of month.
Cell A3, I would like to identify the number of UK Bank Holidays for the given full month from cell A1.

Would I need a separate sheet to list the number of UK Bank Holidays (eg cell A1: December 2023, cell B1: 2) for the given month, and then use a VLOOKUP (or other) formula to identify? If so, please could you assist with the formula.

Best wishes,
Ketan

 

[Anonymous1378]

There are plenty of methods to do this, of which your lookup approach would work.

Another one I could suggest in A3 with your existing formula in A2 is:

=A2-NETWORKDAYS(A1,EOMONTH(A1,0),[range_of_cells_containing_UK_bank_holidays])

 

[fintail99]

Thank you @Anonymous1378 , that works brilliantly. Much appreciated.

Best wishes,
Ketan