If a date falls on a uk holiday date

H

happy_smiler1

New member
Joined
Aug 28, 2012
Messages
67
Reaction score
0
Points
0
Excel Version(s)
office 365
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
 

Attachments

  • Book2.xlsx
    8.5 KB · Views: 19
Hi @happy_smiler1
For me, you are not very clear.
Does this solution work for you, attached to a file?
 

Attachments

  • happy_smiler1-navic-10322.xlsx
    9.9 KB · Views: 9
What are you up for, VBA Power Query, or does it have to be formulas?
 
Bob Phillips said:
What are you up for, VBA Power Query, or does it have to be formulas?
Click to expand...

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
 
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:
You must log in or register to reply here.
Back
Top