Performing EDATE on a Range of Dates, Then Checking if any of the generated dates Fall Between Two Dates

[ejw412]

Hello all -

The title to my post kind of says what I'm trying to do. I have a column of dates that are the first dates for the year that recurring bills will come due. The comparison that I'm trying to do is to look at that entire column, figure out the same day for each subsequent month for each date, then determine if any of those dates fall between two comparison dates. Here's what this would look like, because it's probably easier to visualize:

First Invoice Due Date	Compare Start Date	Compare End Date	Subsequent Invoice Due?
1/10/2020	3/12/2020	3/15/2020	True
1/15/2020	3/5/2020	3/7/2020	False
1/20/2020	3/17/2020	3/19/2020	False
1/30/2020	4/11/2020	4/16/2020	True

So 3/12/2020|3/15/2020 would match 1/15/2020, and 4/11/2020|4/16/2020 would also match 1/15/2020

Here's the catch - i need to do this all at once, to see if ANY of the First Invoice Due Dates fall within ANY of the Compare (subsequent) ranges, when adjusted with a function like EDATE, something like this:

sumproduct((edate($P$8:$P,row($1:$13)) >= F$3)*(edate($P$8:$P,row($1:$13)) <= (F$4)))

This causes sumproduct to throw an error about array sizes not matching, because they don't. Anyone have any ideas how I can achieve what I'm after here? If more clarification is needed, please let me know.

Thanks in advance for any help!

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D
1	First Invoice Due Date	Compare Start Date	Compare End Date	Subsequent Invoice Due?
2	10/01/2020	12/03/2020	15/03/2020	TRUE
3	15/01/2020	05/03/2020	07/03/2020	FALSE
4	20/01/2020	17/03/2020	19/03/2020	FALSE
5	30/01/2020	11/04/2020	16/04/2020	TRUE
New project

Cell Formulas
Range	Formula
D2:D5	D2	=SUMPRODUCT((DAY($A$2:$A$5)>=DAY($B2))*(DAY($A$2:$A$5)<=DAY($C2)))>0

 

[ejw412]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D
1	First Invoice Due Date	Compare Start Date	Compare End Date	Subsequent Invoice Due?
2	10/01/2020	12/03/2020	15/03/2020	TRUE
3	15/01/2020	05/03/2020	07/03/2020	FALSE
4	20/01/2020	17/03/2020	19/03/2020	FALSE
5	30/01/2020	11/04/2020	16/04/2020	TRUE
New project

Cell Formulas
Range	Formula
D2:D5	D2	=SUMPRODUCT((DAY($A$2:$A$5)>=DAY($B2))*(DAY($A$2:$A$5)<=DAY($C2)))>0

Hi Fluff -

Thanks so much for replying! I thought about doing things this way at first but months like february with an irregular number of days scared me off. Upon closer inspection, it looks like this will work perfectly. Really appreciate your help - thanks!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ejw412]

Hey Fluff -

So I just realized an issue with this formula. Say that my Compare Start date is 4/27/20 and my Compare End date is 5/3/2020. The original invoice date is 1/30/2020. In this case, I should get a "true" result, as the subsequent invoice date would be 4/30/2020, but the DAY() functions would return "27" and "3", thus returning a FALSE on the comparison as 30 is greater than 27 but not less than 3.

Thoughts here?

 

[Fluff]

I suspect there must be an easier way, but how about
=SUMPRODUCT((DATE(1,2,DAY($A$2:$A$5))>=DATE(1,IF(MONTH(C2)>MONTH(B2),1,2),DAY($B2)))*(DATE(1,2,DAY($A$2:$A$5))<=DATE(1,IF(MONTH(C2)>MONTH(B2),3,2),DAY($C2))))>0

 

[ejw412]

Thanks again for helping out.

Unfortunately, that doesn't seem to work either, as it returns several trues where there should be falses. Any other ideas? I can't seem to solve this problem!

 

[Fluff]

Can you please provide a sample using the XL2BB add-in, that shows the problems.

 

[ejw412]

Here you go:

Book1
	A	B	C	D	E	F	G	H
1	First Invoice	Amount						should hit here only
2	2/14/20	$2,000			Period 05	Period 05	Period 05	Period 05
3					4/20/20	4/27/20	5/4/20	5/11/20
4				Rent	0	2000	0	2000
Sheet1

Cell Formulas
Range	Formula
E4:H4	E4	=SUMPRODUCT(($B2:$B5)*(DATE(1,2,DAY($A$2:$A$5))>=DATE(1,IF(MONTH(E3+6)>MONTH(E3),1,2),DAY(E3)))*(DATE(1,2,DAY($A$2:$A$5))<=DATE(1,IF(MONTH(E3+6)>MONTH(E3),3,2),DAY(E3+6))))

 

[Fluff]

This has got absolutely nothing to do with your original question, so you will need to start a new thread.