CODEWITHSUNDEEP
exceldaterangeformulaoverlapping
Ashley Marin
Ashley Marin

Reputation: 3

Excel Formula to return earliest date and latest date within a date range

UPDATED sample input and output table

Please see attached image for sample data (table on left) and intended outcome (table on right).

I tried using the sample formulas on this website provided for similar questions but was unsuccessful in tailoring it to my specific problem. Hoping to get some inputs and guidance specific to my case.

I have 1000 rows of data in a table similar to what is shown in the image.

I want to consolidate the rows to show Columns 1, 4, and 5 based on the overlapping date ranges in Columns 2 & 3 (i.e. combine the rows that have overlapping date range) to get a table similar to the one on the right where it only shows the earliest date and latest date for each group.

For reference, below is what I have so far. This formula gives me everything I need for the second column of my output (Start Date) except that it returns the first value that matches all the criteria rather than return the lowest value (unsure where to add the MIN function in this formula).

=IFERROR(INDEX([Start Date],MATCH(1, IF([Start Date]>=[@[Start Range]], IF([Start Date]<=[@[End Range]], IF([Key1]=[@Key1],1),0),0),0)),"")

Please let me know if you need any further information. Thank you!

Upvotes: 0

Views: 1444

Answers (1)

Jerry Jeremiah
Jerry Jeremiah

Reputation: 9618

MATCH can only return one row number but you need an array of all the rows that match so that you can pick the smallest/largest - you need an array formula.

You have multiple criteria, but AND and OR don't work in array formulas so a trick is needed to simulate AND and OR:

AND(a=b,c=d) and OR(a=b,c=d) treats a=b and c=d as TRUE/FALSE values but it's just as valid to think of them as FALSE=0 and TRUE<>0. If you do that then (a=b)*(c=d) is the same as using AND and (a=b)+(c=d) is the same as OR.

For the start, we are using MIN to find the smallest item that matches. The ones that match are numbers displayed as dates. What about the ones that don't match? We need a number larger than any conceivable date. The number 2958465 is 31/12/9999 (the largest date) so we can use anything larger than that - we'll use 9999999 which is definitely not a valid date.

For the end, we are using MAX to find the largest item that matches. The ones that match are numbers displayed as dates. For the ones that don't match, we need a number smaller than any conceivable date. The number 0 is the smallest valid date so we'll use -1 which is definitely not a valid date.

So the array formulas we need to use are:

For the ID, put this formula in G2 and then copy the result down for every row of original data:

=A2

For the start date, put this formula in H2 and press Control-Shift-Enter instead of just Enter and then copy the result down for every row of original data:

=MIN(IF((D2>=B:B)*(D2<=C:C)*(A2=A:A)+(E2>=B:B)*(E2<=C:C)*(A2=A:A),D:D,9999999))

For the end date, put this formula in I2 and press Control-Shift-Enter instead of just Enter and then copy the result down for every row of original data:

=MAX(IF((D2>=B:B)*(D2<=C:C)*(A2=A:A)+(E2>=B:B)*(E2<=C:C)*(A2=A:A),E:E,-1))

When a cell has been entered as an array formula with Control-Shift-Enter, Excel puts curly braces around it so that you can tell that it is an array formula.

Now, this isn't perfect because you wanted a separate results table with no duplicates and this one has lots of duplicates. You can use the advanced filter to show only unique values - you have the option of filtering in place which hides rows or copying it to another location.

If this separate results table is on the same sheet as the original data, filtering in place will hide rows from the original data too so you might be better to put this separate table on a different sheet.

If you filter by copying to a new place (perhaps just to the right) then you can just hide the columns of the results with the duplicates - just watch out because the copy you made is just values and they won't automatically update.

Upvotes: 0

Related Questions