Move amount to prior working day (assuming that isn't a holiday as well) if due date is a holiday

[losincog]

Hi there,

We have invoices that we pay as per contractual terms. But if due date falls on a holiday, we pay the invoice 1 working day earlier. I'm not sure how to adjust this with a formula

Tabs:

Holiday Adjusted - Would like for this to pull in payment value like with the Pmt Date tab but adjusted for holidays, i.e., pay a working day early if falls on a Holiday.

Pmt Date - payments as per invoice due date, split by country and currency

Holiday - list of holidays per country

Thanks

Sketched a sample in dropbox

Holiday Adjusted Dummy Layout.xlsx

Shared with Dropbox

www.dropbox.com

 

[jasonb75]

Looks like this has already been answered here

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

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...

www.mrexcel.com

 

[losincog]

Thanks. This is a different layout, compared to that thread's format and different data type. I've been having trouble extrapolating the original thread's layout for the next 12 months. So a workaround has been in a different layout. So I need a different solution and slightly different requirement to the original thread