+ Reply to Thread
Results 1 to 6 of 6

Highlight UK Bank Holiday

  1. #1
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Highlight UK Bank Holiday

    Hi,

    Does anybody know a way to highlight a range of cells when that day falls on a UK Bank Holiday? I'm thinking formula in conditional format but not sure if that's right or where to start, could possibly list the holidays on a separate sheet and reference to that or can excel pull this from a calendar somewhere?

    Regards
    Tez

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Highlight UK Bank Holiday

    I'm thinking that this could be accomplished with some VBA. Suggest you upload a sample workbook that contains a range of dates to be reviewed and in that workbook on a separate worksheet a list of UK Bank holidays.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Highlight UK Bank Holiday

    use a seperate sheet with all the dates of any holidays you want to add in - BUT it will need to extend for all years needed to cover and be maintained in future
    then in conditional formatting you can use a countif()

    lets say you are putting dates into A2 sheet1
    and the reference is in A2 sheet2 to A?
    then

    conditional formatting formula
    =COUNTIF( Sheet2!A1:A100, sheet1!A2)

    Then anything greater than a count of Zero - will be TRUE and coloured

    Note in UK dates are different between , england & wales, scotland and NI
    https://www.gov.uk/bank-holidays
    Last edited by etaf; 04-07-2019 at 05:48 AM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Highlight UK Bank Holiday

    Adding a little something to etaf's suggestion, this workbook contains a set of formulas that calculates the dates of all of the regular bank holidays for a 3 year period, starting from new year's day of the year entered into B2.

    Note that this only includes the regular England & Wales bank holidays, not any additional special occasions such as royal weddings, or those exclusive to Scotland or NI.

    The extra dates for Scotland and NI can be formulated easily enough if needed, but any one off dates would need to be added manually.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: Highlight UK Bank Holiday

    Thanks all for your responses, I was all set to upload an example but tried the suggestion of etaf and it worked as required.

    alansidman thanks for your suggestion, i like the power and versatility of vba and figured it may work for what I needed. In this instance it wasn't required.

    jason.b75 not tried your suggestion yet but sounds like something i'll try, just to streamline the amount of sheets required.

    Again thanks all, uploaded an example for others to see the end result and how it works.

    On sheet Rota! bank holidays highlighted in yellow and close of business in red, just need to update sheet UK Bank Hols! each year and it'll work fine.

    Thanks
    Attached Files Attached Files

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Highlight UK Bank Holiday

    you are most welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Post bank transactions to bank Recon as individual amounts instead of sums
    By SjMaxwell in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-16-2018, 03:15 AM
  2. Bank Holiday Exclusion counting Days
    By K-Linerz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-15-2018, 03:59 PM
  3. including bank holiday's within my workbook
    By ellie247 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-30-2015, 08:23 AM
  4. Adding Bank Holiday pay into calculations HELP!
    By Learning ExL in forum Excel General
    Replies: 8
    Last Post: 04-27-2015, 02:47 PM
  5. check if todays date is bank holiday
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2014, 07:13 AM
  6. [SOLVED] IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates
    By jowarks in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-05-2013, 05:42 AM
  7. holiday calculation around bank holidays
    By y0rk1e72 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-19-2012, 06:55 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1