Calculate Payment Date - Excel or Power Query

[crazypanda]

I need help calculating payment date (either in Power Query or Excel - the data has been cleaned up by PQ, but happy to do a formula just in table of excel to get this part.
I have a Due Date column. I need a Payment Date column.
Payments are made on Thursdays, paying bills that have already passed the due date, and up to including the following Wednesday.
Examples:
ie Today is 13/Feb/24. IF a Due date is 10/Feb (already passed due date), it will get paid on 15Feb. If Due Date is 18/Feb (up to and including following Wednesday), it will also be paid on 15Feb.
Help would be heaps appreciated

 

[awoohaw]

Not really sure what your past due on 10 February means regarding how to calculate when to calculate a payment date that you know you need to pay ASAP.
So, i've ignored that.

So, here is my guess at it:

Book1
	A	B	C
1		Due Date	Payment Date
2		Sat 2024-02-10	Thu 2024-02-08
3		Sun 2024-02-11	Thu 2024-02-08
4		Mon 2024-02-12	Thu 2024-02-08
5		Tue 2024-02-13	Thu 2024-02-08
6		Wed 2024-02-14	Thu 2024-02-08
7		Thu 2024-02-15	Thu 2024-02-08
8		Fri 2024-02-16	Thu 2024-02-15
9		Sat 2024-02-17	Thu 2024-02-15
10		Sun 2024-02-18	Thu 2024-02-15
11		Mon 2024-02-19	Thu 2024-02-15
12		Tue 2024-02-20	Thu 2024-02-15
13		Wed 2024-02-21	Thu 2024-02-15
14		Thu 2024-02-22	Thu 2024-02-15
15		Fri 2024-02-23	Thu 2024-02-22
16		Sat 2024-02-24	Thu 2024-02-22
17		Sun 2024-02-25	Thu 2024-02-22
Sheet4

Cell Formulas
Range	Formula
C2:C17	C2	=WORKDAY.INTL(B2,-1,"1110111")

 

[KRice]

If I'm following correctly, this might be what you want:

Book1
	A	B	C
1		Due Date	Payment Date
2		Sat 10-Feb-24	Thu 15-Feb-24
3		Sun 11-Feb-24	Thu 15-Feb-24
4		Mon 12-Feb-24	Thu 15-Feb-24
5		Tue 13-Feb-24	Thu 15-Feb-24
6		Wed 14-Feb-24	Thu 15-Feb-24
7		Thu 15-Feb-24	Thu 15-Feb-24
8		Fri 16-Feb-24	Thu 15-Feb-24
9		Sat 17-Feb-24	Thu 15-Feb-24
10		Sun 18-Feb-24	Thu 15-Feb-24
11		Mon 19-Feb-24	Thu 15-Feb-24
12		Tue 20-Feb-24	Thu 15-Feb-24
13		Wed 21-Feb-24	Thu 15-Feb-24
14		Thu 22-Feb-24	Thu 22-Feb-24
15		Fri 23-Feb-24	Thu 22-Feb-24
16		Sat 24-Feb-24	Thu 22-Feb-24
17		Sun 25-Feb-24	Thu 22-Feb-24
Sheet1

Cell Formulas
Range	Formula
C2:C17	C2	=IF(B2-1<=TODAY(),WORKDAY.INTL(B2,1,"1110111"),WORKDAY.INTL(B2+1,-1,"1110111"))