IF = currency is X and due day is 1, return day as 2

Hi there

We normally pay certain types of creditors everyday but have recently changed to staggered payment days depending on currency, so want to adapt my existing workbook to reflect that:

If the currency is GBP:

Invoices due for payment Tuesday will be paid on Tuesday
Invoices due for payment on Wednesday & Thursday will be paid on Wednesday
Invoices due for payment Friday to Monday will be paid on Friday

If the currency is non-GBP:

Invoices due for payment on Monday will be paid on Monday
Invoices due for payment on Tuesday & Wednesday will be paid on Tuesday
Invoices due for payment on Thursday to Sunday will be paid on Thursday

Confused as to how I would do this :/

p.s. Dates tab in the attached book helps to pull in payments due on Saturday and Sunday into Friday

Payment Date is based on col G in data tab

Thanks Cemo, it did not twig for me that I could use it like that

How do I fix the GBP when the payment is on a Sunday. Anything due on Fri-Mon for GBP will be paid on Fri but for Sunday it's showing it will be paid on Thursday on the new sheet, how do I fix this? row M?:
Attachment 681112

I think I have it adjusted correctly, changing the first argument to -2, instead of -3:

=IF(D641="GBP",
CHOOSE(WEEKDAY(E641,2),E641-3,E641,E641,E641-1,E641,E641-1,E641-2),
CHOOSE(WEEKDAY(E641,2),E641,E641,E641-1,E641,E641-1,E641-2,E641-3))

Hello

I was a bit short-minded in what i was thinking for the above, as I needed to think about 1) bank/public holidays and 2) never pay early over month-end, to optimise cash position on the balance sheet at month-end.

The main criterions in the main post applies but is what happens 1) when there is a bank/public holiday 2) at month-end

1) Bank Holiday - If the EUR invoice due payment date falls on a Wednesday, as per main criteria it will be paid on Tuesday normally, along with invoices due for payment on Tuesday. But if Tuesday is a bank/public holiday, it will instead be paid on the Monday, along with the Monday due invoices, to ensure the beneficiary doesn't get paid late.

2) Month-End - If the EUR invoice due payment date falls on a Wednesday, as per main criteria, it will be paid on Tuesday normally, along with invoices due for payment on Wednesday. But if Wednesday is the 1st of Month Y, we would pay it on Wednesday, rather than early on the last day of Month X

I've attached the workbook with the good results from the thread's original aim (always "doc" currency)

I was thinking maybe a separate Dates tab that has lookup table for bank/public holidays that I can adjust, for the bank holidays issue?