Avoid overlapping dates ranges (grouping)

Hi and welcome to the forum,

I do not think it will be aesy to obtain with formulas only (and especially with easy formulas).
I'd use such a simple (I focused on easy understanding, so it's not optimized for speed) VBA code:

Please Login or Register  to view this content.

Two notes
- I followed presented layout of data - for instance no header row
- dates in columns B and C has to be recognized by Excel as dates. Change formatting of one of these cells to General, and if you see a number like 40000+ in a cell, then it is a date. If it's still 04/2008 then you probably has text in a cell (which only looks like a date).

See attached

I'm with Kaper

I do not think it will be aesy to obtain with formulas only (and especially with easy formulas).

that said, it seemed an interesting challenge -- these types of calcs are not my forte, I'm sure there's a MMULT type approach out there that would work, or some better logic.

If we assumed your start dates are in B3:B12, and your end dates in C3:C12, enter 1 into A3 {first entry} and the below can then be applied to A4, and copied down - mirroring your expected results.

Please Login or Register  to view this content.

edit: updated 3pm UK, superfluous TRANSPOSEs removed, and removed Array entry requirement

Nice one!
But if I can comment:
Even after transposes removed and no array committment required - still wouldn't call it an easy formula

Anyway: Rep+ from me

Thanks; yes, it's Volatile too (given use of INDIRECT) -- so I wouldn't be arguing it should be used...

I just found the challenge interesting... and spent way too long that is acceptable trying to work out a suitable approach... in the end it's relatively straightforward logic:

- create an array of values for all preceding rows such that the end date serial is appended as a decimal remainder of the associated label value
- from the above array extract the "max" value for each label
- above achieved via an outer SMALL in conjunction with a dynamic k array, the k array being determined by a COUNTIF relative to any / all labels used to that point
- the date serial values can be removed from the SMALL results c/o MOD leaving just the date "decimal", in turn reverted to a date serial (c/o * and round)
- the final test is then to see which is the first label with a current end date preceding the criteria (c/o MATCH) and use that as the result
- if there is no such result a new label must be required (determined as max of preceding labels + 1)

Note:
I realise now, having typed out the above, that my final test is incorrect as it is testing the end date rather than the start date of the current row, and thus should in fact read:

Formula:

Please Login or Register  to view this content.

change being first cell reference now reads $B4 as opposed to $C4, remainder unchanged...

just so happens that with the sample data you get the same results, either way ;-)