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

ejw412

New Member
Joined
May 19, 2020
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGHIJKLM
1BillFirst InvoiceAmount
2Rent2/14/20$2,000 Period 05Period 05Period 05Period 05Period 06Period 06Period 06Period 06
3Taxes1/30/20$1,000 Week starting4/20/204/27/205/4/205/11/205/18/205/25/206/1/206/8/20
4Internet1/10/20$250 Expenses$ -$1,000.00 $250.00 $2,000.00 $ -$1,000.00 $ -$2,250.00
Sheet1
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
613
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLM
1BillFirst InvoiceAmount
2Rent14/02/20202000Period 05Period 05Period 05Period 05Period 06Period 06Period 06Period 06
3Taxes30/01/20201000Week Starting20/04/202027/04/20204/05/202011/05/202018/05/202025/05/20201/06/20208/06/2020
4Internet10/01/2020250Expenses $ - $1.000,00 $ 250,00 $2.000,00 $ - $1.000,00 $ - $2.250,00
Sheet3
Cell Formulas
RangeFormula
E3E3=F3-7
G3:M3G3=F3+7
F4:M4F4=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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,600
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,493
Messages
5,548,370
Members
410,828
Latest member
A9Bosv3
Top