Pull date 1 working day earlier, if original date falls on 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

[bebo021999]

Is it possible to sort holidays by countries?

[losincog]

Is it possible to sort holidays by countries?

Hi

Sorry, not sure what you mean. I've categorised the Holidays tabs in Holiday (Holiday name), Country and Date, is that not what you mean?

[bebo021999]

This is what I mean

[losincog]

This is what I mean

Oh ok, how would that help with populating payment values with adjusted payment dates (holiday adjustment) in the first tab? Sorry, confused (Excel does that to me a fair bit!)

[JeteMc]

As it appears that values are manually entered on the Pmt Date sheet, what about using conditional formatting to alert the person inputting the data that a date is a holiday for a certain country?
If that is acceptable select the range E6:MW285 on the Pmt Date sheet and use the following formula as a New Rule:

Please Login or Register  to view this content.

Select a suitable fill to contrast with blue (i.e. yellow or red)
Let us know if you have any questions.