Excel Formula HELP

[ItalianPlatinum]

Hello,
I am struggling to find a way to get what I need. I need a way to give me a working day, then on Friday's to give me the day before the next business day. The curve ball i can't tackle is if the next day is a holiday to give me the holidays date. here is my data set.

On 03/19/20 Thursday display 03/19/20 (normal working day)
On 03/20/20 Friday display 03/22/20 (normal weekend)
On 12/31/19 Tuesday display 01/01/20 (mid week Holiday)
On 05/22/20 Friday display 05/25/20 (Monday Holiday after weekend)
On 04/09/20 Thursday display 04/12/20 (Friday holiday before weekend)

Thanks in advance

 

[sandy666]

could you post representative example for all week days AND expected result via XL2BB ?

 

[ItalianPlatinum]

I have never done that before. Is there another way you want me to send my example?

 

[Saba Sabaratnam]

Hi

Enter your holidays in D1:D6. You can

Enter the following formula in Cell B2
=WORKDAY(A2,1,$D$2:$D$6)-1

Copy the down the formula to Cell B6

Please see the layout of my spreadsheet below.

Kind regards

Saba

 

[sandy666]

I have never done that before

Maybe time to learn ? hit link in new post or in my post above

Is there another way you want me to send my example?

sure, post a link to the shared excel file with source data and expected result via googledrive, onedrive or any similar

 

[ItalianPlatinum]

Hi

Enter your holidays in D1:D6. You can

Enter the following formula in Cell B2
=WORKDAY(A2,1,$D$2:$D$6)-1

Copy the down the formula to Cell B6

Please see the layout of my spreadsheet below.
View attachment 9426

Kind regards

Saba

Hi Saba that may work. The holidays would need to be entered manually? Does excel have no way to know them?

 

[Saba Sabaratnam]

I think that Excel would not know holidays in a country / state automatically as they vary every year and between countries and states

Kind regards

Saba