Test If Subsequent Invoice Date Falls On a Given Week, Based off of Original Invoice Date

[ejw412]

Hello all -

I am trying to figure out if, given an initial invoice date for a recurring service, I can run a comparison on future weeks to figure out if subsequent invoices will be due on that week. The main catch is that I need to be able to get ALL invoice payments that would be due that week (for cashflow purposes), totaling them in their corresponding cells. How my table SHOULD LOOK is below. The approach I have tried and thus far failed with is running a SUMPRODUCT function, wherein I run an EDATE function taking the dates in column B and then attempting to go out 12 months into the future. For example, this is the formula in F4:

=SUMPRODUCT((C2:C4)*(EDATE(B2:B4,ROW($1:$13)) >= F$3)*(EDATE(B2:B4,ROW($1:$13)) <= F$3+6))

This throws an array size error. I've been advised to try other methods, such as DAY() comparisons, but if the week starts in one month and ends in the next month, then that fails the DAY() comparison. If anyone can provide any assistance at all, it would be greatly appreciated. Thanks in advance!!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Bill	First Invoice	Amount
2	Rent	2/14/20	$2,000			Period 05	Period 05	Period 05	Period 05	Period 06	Period 06	Period 06	Period 06
3	Taxes	1/30/20	$1,000		Week starting	4/20/20	4/27/20	5/4/20	5/11/20	5/18/20	5/25/20	6/1/20	6/8/20
4	Internet	1/10/20	$250		Expenses	$ -	$1,000.00	$250.00	$2,000.00	$ -	$1,000.00	$ -	$2,250.00
Sheet1

 

[GraH]

Hello,

Not what you are looking for, I just wanted to give it a go and failed .

The error comes from EDATE not being able to take a range as input for the first argument "start date". However referencing a single date works. So repeating the formula for each row variable in the Billing table alos works. I do assume there are potentially more rows in that table.
They key is tuning EDATE (or replacing by another funtion?) so you can feed it the range of dates. I have no clue whatsoever how to do that. Got stuck with this.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Bill	First Invoice	Amount
2	Rent	14/02/2020	2000			Period 05	Period 05	Period 05	Period 05	Period 06	Period 06	Period 06	Period 06
3	Taxes	30/01/2020	1000		Week Starting	20/04/2020	27/04/2020	4/05/2020	11/05/2020	18/05/2020	25/05/2020	1/06/2020	8/06/2020
4	Internet	10/01/2020	250		Expenses	$ -	$1.000,00	$ 250,00	$2.000,00	$ -	$1.000,00	$ -	$2.250,00
Sheet3

Cell Formulas
Range	Formula
E3	E3	=F3-7
G3:M3	G3	=F3+7
F4:M4	F4	=SUMPRODUCT(($C$2)*(EDATE($B$2,ROW($1:$13)) >= F$3)*(EDATE($B$2,ROW($1:$13)) <= F$3+6)) +SUMPRODUCT(($C$3)*(EDATE($B$3,ROW($1:$13)) >= F$3)*(EDATE($B$3,ROW($1:$13)) <= F$3+6)) +SUMPRODUCT(($C$4)*(EDATE($B$4,ROW($1:$13)) >= F$3)*(EDATE($B$4,ROW($1:$13)) <= F$3+6))

 

[Tetra201]

See if the following modified formula works for you:

=SUMPRODUCT(($C$2:$C$4)*(EDATE(+$B$2:$B$4,COLUMN($A:$M)) >=F$3)*(EDATE(+$B$2:$B$4,COLUMN($A:$M)) <=F$3+6))