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!

 

[ejw412]

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

Ok - if you think it best that's no problem. How would you like me to go about formatting that?

 

[Fluff]

How would you like me to go about formatting that?

I'm afraid I don't understand what you mean by this.

 

[ejw412]

I'm afraid I don't understand what you mean by this.

Sorry - just meant that I'm still trying to accomplish the same task, it's just that instead of having two date columns (as originally posted, for brevity) i'm adding 6 days to the dates in question to see if an invoice would be due that week, based off of the original date, and if so have that represented in that subsequent date's column.

So by formatting, I just meant is there a better way to restate the question more appropriately in a separate post? Not trying be dense, just new to the forum and figuring out the proper posting etiquette.

 

[Fluff]

You are always better off providing accurate information of what you are trying to do.
"Simplifying" things, will normally get you something that doesn't work on your actual data & therefore waste your time & ours.

 

[ejw412]

You are always better off providing accurate information of what you are trying to do.
"Simplifying" things, will normally get you something that doesn't work on your actual data & therefore waste your time & ours.

Got it. Will repost separately with accurate data and intent! Thanks for guidance.

 

[Fluff]

Thanks

 

[ejw412]

No problem. Here is the new thread. Hopefully can sort this problem out.

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