including bank holiday's within my workbook

[ellie247]

Hi All,

Hoping someone can help me...

I have a workbook where I need to ensure bank holiday dates show my workbook looks like the below

A1 = the Day(as an example Tuesday) B1 = the date as an example (01/09/2015 which is a Tuesday)

A9 = IF($A$1="Monday",$B$1,IF($B$1-1='Bank Holidays'!C:C,'Bank Holidays'!C:C,"")) I need the date of the 31/08/2015 to show in cell A9 as the 01/09/2015 was the day after the bank holiday reason I placed $b$1-1 into the formula to deduct a day so that is my second condition within the formula, my first condition will leave a blank cell if the 31/08/2015 wasn't a bank holiday and if it doesn't match cell B1. Within the Bank Holidays C:C range I have all the bank holidays listed.

Can anyone see where I am going wrong?

Sorry my post seems to have gone a bit all over the place apologies.

Thanks
Ellie

[Kaper]

Hi Ellie,

Basically the description sounds clear, but ...
How about attaching a sample workbook?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic

[ellie247]

Thank you will give that a go now

[ellie247]

Here is a working book attachment have entered everything onto one sheet before and after. Hope it helps.

Deleted the attachment and have loaded it again hopefully it's a bit clearer. I basically always need to ensure the bank holiday date shows in column B against the day of the week A

Thanks
Ellie

[ellie247]

can anyone help with this?

[Kaper]

Try B8:

Please Login or Register  to view this content.

The added part is checking if a "day before B1" is on the holiday list and if its weekday is as listed in current row in column A. If so, the day before B1 date is displayed.
Have I understood the need correctly? (or at least was close enough you can adopt it for your needs?) See attached.