# Move to prior working day if it's weekend or holiday in country

#### losincog

##### New Member
Hello

We have contractual payment terms that we need to adhere to, i.e., pay on time but on two occasions we pay early:

if the payment due date falls on a weekend, it should get paid early on the Friday just gone.
Also if payment due date falls on a holiday, it gets paid early, on the previous working day (if holiday on a Monday and due date is on that Monday, it will get paid on the previous Friday. If due date is a Monday but the previous Friday is a holiday also, it will get paid on a Thursday)

Not sure how to do this, especially as if have different holiday dates in different countries?

Holidays tab has the list of holidays by country and date as shown in pic. The Payment Date tab is where I need to populate a entries in the Amended Pay Date column by adjusting the Pay Date with the holidays tab info for whichever country is in Col A

Thanks

#### Attachments

• Payment Date.JPG
107.3 KB · Views: 9
• Holidays.JPG
80.6 KB · Views: 8

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Eric W

##### MrExcel MVP
Welcome to MrExcel!

I put your list of holidays on Sheet5 in my test workbook. Then you can use this formula:

Book1
ABCD
1CountryPay DateAmended Pay Date
2UK9/18/2020Fri9/17/2020
3AT8/14/2020Fri8/13/2020
4SG9/18/2020Fri9/17/2020
5BE8/14/2020Fri8/13/2020
6AT8/24/2020Mon8/21/2020
7US8/24/2020Mon8/21/2020
8NL2/20/2021Sat2/19/2021
9US2/20/2021Sat2/19/2021
10UK11/1/2020Sun10/30/2020
11HK11/1/2020Sun10/30/2020
12DE8/27/2020Thu8/27/2020
13US7/8/2021Thu7/8/2021
14SG8/27/2021Fri8/27/2021
15BE7/8/2021Thu7/8/2021
16UK9/15/2020Tue9/14/2020
17NL9/15/2020Tue9/14/2020
18AT8/26/2020Wed8/26/2020
19BE8/26/2020Wed8/26/2020
Sheet4
Cell Formulas
RangeFormula
C2:C19C2=TEXT(B2,"ddd")
D2:D19D2=WORKDAY(B2+1,-1,IF(Sheet5!\$A\$2:\$A\$9=A2,Sheet5!\$C\$2:\$C\$9,1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Brill, thanks!

#### Eric W

##### MrExcel MVP
Happy to help! Thanks for the update.

Replies
2
Views
137
Replies
2
Views
448
Replies
2
Views
180
Replies
22
Views
358
Replies
29
Views
1K

1,130,157
Messages
5,640,462
Members
417,144
Latest member
Pitsi Mokoele

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back