Results 1 to 5 of 5

Thread: If a date falls on a uk holiday date

  1. #1
    Acolyte happy_smiler1's Avatar
    Join Date
    Aug 2012
    Posts
    55
    Articles
    0
    Excel Version
    office 365

    If a date falls on a uk holiday date



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    In my spreadsheet attached, I have a gift date, based against the UK bank holiday date, I wanted to check if the gift date lands on the bank holiday date, if it does, then in date gift received would then add 2 days to the gift date, if it doesnt then it would just display the gift date. I would need this for every bank holiday in the uk, however as a starting point it would be helpful if anyone could give me some guidance.

    TIA
    Attached Files Attached Files

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    887
    Articles
    0
    Excel Version
    Excel 2013
    Hi @happy_smiler1
    For me, you are not very clear.
    Does this solution work for you, attached to a file?
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,682
    Articles
    0
    Excel Version
    O365
    What are you up for, VBA Power Query, or does it have to be formulas?

  4. #4
    Acolyte happy_smiler1's Avatar
    Join Date
    Aug 2012
    Posts
    55
    Articles
    0
    Excel Version
    office 365
    Quote Originally Posted by Bob Phillips View Post
    What are you up for, VBA Power Query, or does it have to be formulas?
    Power query sounds like it would be easier to set it up in the background if you can guide me as to how it would be achieved as I haven't really used power query.

    Thanks

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,618
    Articles
    0
    Excel Version
    365
    Test this in cell B4:
    Code:
    =IF(WORKDAY.INTL(A4-1,1,"0000000",$B$2)<>A4,A4+2,A4)
    copied down.
    Currently it looks at cell B2 for the holiday, but this can be a range of cells containing a list of holiday dates instead.
    It does not further check whether the new date is also a bank holiday, however this formula should never return a bank holiday date:
    Code:
    =IF(WORKDAY.INTL(A4-1,1,"0000000",$B$2)<>A4,WORKDAY.INTL(A4,2,"0000000",$B$2),A4)
    Last edited by p45cal; 2019-11-16 at 01:36 AM.

Posting Permissions

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