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

losincog

New Member
Joined
Aug 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
    Payment Date.JPG
    107.3 KB · Views: 54
  • Holidays.JPG
    Holidays.JPG
    80.6 KB · Views: 54

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top