Problem with Dates in Excel (scheduling a day in a week each month)

[financefinance]

In cell B10 I have a value of $10,000
In cell C10, I have the value 26
In this example, I am intending to create a schedule of payments in whichever week the 26th falls in each month throughout the year

In cell D6 I have the week ending date 12/25/2020
In cell E6 I have the week ending date 1/1/2021 (in this example, I am expecting the payment to be in the week in 1/1/21)
In cell F6 I have the week ending date 1/8/2021 ... and so on

I tried the following formulas, but neither is resulting a value in the week of 1/1/2021 (I understand why I am not getting the value; I am not sure of the fix)
=IF(AND($C10>(DAY(D$6)-7),$C10<=DAY(D$6)),$B10,0)

=IF(AND(DATE(YEAR(D$6),MONTH(D$6),$C10)>(D$6-7),DATE(YEAR(D$6),MONTH(D$6),$C10)<=D$6),$B10,0)

Prefer a formula solution. Appreciate your help!

 

[Dave Patton]

T202012c.xlsm
	B	C	D	E	F	G	H	I
6		Fri 18-Dec-20	Fri 25-Dec-20	Fri 01-Jan-21	Fri 08-Jan-21	Fri 15-Jan-21	Fri 22-Jan-21	Fri 29-Jan-21
7			0	26	0	0	0	26
8
9
10	10000	26
3a

Cell Formulas
Range	Formula
D7:I7	D7	=AND(DATE(YEAR(C6),MONTH(C6),$C$10)>C6,DATE(YEAR(C6),MONTH(C6),$C$10)<D6)*$C$10

 

[Dave Patton]

T202012c.xlsm
	A	B	C	D	E	F	G	H	I	J
6			Fri 18-Dec-20	Fri 25-Dec-20	Fri 01-Jan-21	Fri 08-Jan-21	Fri 15-Jan-21	Fri 22-Jan-21	Fri 29-Jan-21	Fri 05-Feb-21
7				0	10000	0	0	0	10000	0
8
9
10		10000	26
3a

Cell Formulas
Range	Formula
D7:J7	D7	=AND(DATE(YEAR(C6),MONTH(C6),$C$10)>C6,DATE(YEAR(C6),MONTH(C6),$C$10)<D6)*$B$10

 

[financefinance]

Thanks. The solution didn't suit my needs as I may have simplified my example too far. It may be a little more complicated because I am looking the value up via SUMPRODUCT from a Monthly Schedule. I am choosing the day within the month I would like the payment to fall (i.e., the 26th) and trying to create a weekly schedule.

 

[jasonb75]

@Dave Patton I haven't evaluated your second post, but the first one doesn't work correctly with days early in the month (try 3 instead of 26).

@financefinance does this help?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
6			25/12/2020	01/01/2021	08/01/2021	15/01/2021	22/01/2021	29/01/2021	05/02/2021	12/02/2021	19/02/2021	26/02/2021	05/03/2021	12/03/2021	19/03/2021	26/03/2021	02/04/2021
7
8
9
10	10000	26	0	10000	0	0	0	10000	0	0	0	10000	0	0	0	10000	0
Sheet1

Cell Formulas
Range	Formula
D10:R10	D10	=IF(OR(DAY(D$6-{0,1,2,3,4,5,6})=$C10),$B10,0)

 

[Dave Patton]

Both versions work. I like concise solutions such as your suggestion.

T202012c.xlsm
	A	B	C	D	E	F	G	H	I	J
6			Fri 18-Dec-20	Fri 25-Dec-20	Fri 01-Jan-21	Fri 08-Jan-21	Fri 15-Jan-21	Fri 22-Jan-21	Fri 29-Jan-21	Fri 05-Feb-21
7				0	10000	0	0	0	10000	0
8				0	10000	0	0	0	10000	0
9
10		10000	26
3a

Cell Formulas
Range	Formula
D7:J7	D7	=AND(DATE(YEAR(C6),MONTH(C6),$C$10)>C6,DATE(YEAR(C6),MONTH(C6),$C$10)<D6)*$B$10
D8:J8	D8	=IF(OR(DAY(D$6-{0,1,2,3,4,5,6})=$C10),$B10,0)

 

[financefinance]

Sorry, repasting image with column and row references

 

[Fluff]

Both versions work.

Not for me they don't, if c6 is early in the month

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
6			18/12/2020	25/12/2020	01/01/2021	08/01/2021	15/01/2021	22/01/2021	29/01/2021	05/02/2021	12/02/2021	19/02/2021	26/02/2021	05/03/2021	12/03/2021
7				0	0	10000	0	0	0	0	0	0	0	0	0
8				0	0	10000	0	0	0	10000	0	0	0	10000	0
9
10		10000	4
Data

Cell Formulas
Range	Formula
D7:O7	D7	=AND(DATE(YEAR(C6),MONTH(C6),$C$10)>C6,DATE(YEAR(C6),MONTH(C6),$C$10)<D6)*$B$10
D8:O8	D8	=IF(OR(DAY(D$6-{0,1,2,3,4,5,6})=$C10),$B10,0)

 

[Dave Patton]

Do you have an amortization schedule?
Can you use a lookup to secure the relevant amount?
Please post relevant information with the forum's XL2BB.

 

[financefinance]

Do you have an amortization schedule?
Can you use a lookup to secure the relevant amount?
Please post relevant information with the forum's XL2BB.

The monthly table is the amortization table. The amounts are not constant, so the relevant amount for each payment on the 26th will change.

Apologies, I was not familiar with XL2BB. I have read about it and will install it on my personal PC this afternoon and try to repost this evening.

Appreciate you trying to assist.