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

ejw412

New Member
Joined
May 19, 2020
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
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 DateCompare Start DateCompare End DateSubsequent Invoice Due?
1/10/20203/12/20203/15/2020True
1/15/20203/5/20203/7/2020False
1/20/20203/17/20203/19/2020False
1/30/20204/11/20204/16/2020True

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!
 
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?
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top