How to create different date ranges based on criteria?

Question

I have a table with two active columns. In Column A I have all the dates between, let`s say, January 1, 2012 to December 31, 2019. In column B I have a corresponding name. For instance:

[ etc.

I want to create ranges based on criteria, like:

The trick is, this should be done by calendar year, which means that during the 8-year period, I should have 8 set of 3 columns (from / to / name), one for each calendar year. If one range covers two years (let's say, November 1, 2012 to February 1st, 2013), the last row of 2012 should read 2012-11-01 to 2012-12-31 while the first row of 2013 will read 2013-01-01 to 2013-02-01

I managed to separate the ranges, but for some reason I am not able to go further and do that for each calendar year. Is there a way to do that?

Answer 1

Let's assume you want to place your 8 set of 3 columns starting from column G and that your list in the range D:F has headers in row 1 and data from row 2 on. In cell G1 write down your first year (2012), in cell H1 "From", in cell I1 "To" and in cell J1 "Name".

Now in cell H2 write this formula:

=IF(IF(OR(IF(AND($E2>=DATE(G$1,1,1),$D2<=DATE(G$1,12,31)),1,0),IF(AND($D2>=DATE(G$1,1,1),$E2<=DATE(G$1,12,31)),1,0)),1,0),MAX($D2,DATE(G$1,1,1)),"")

In cell I2 write this formula:

=IF(IF(OR(IF(AND($E2>=DATE(G$1,1,1),$D2<=DATE(G$1,12,31)),1,0),IF(AND($D2>=DATE(G$1,1,1),$E2<=DATE(G$1,12,31)),1,0)),1,0),MIN($E2,DATE(G$1,12,31)),"")

In cell J2 write this formula:

=IF(AND(H2<>"",I2<>""),F2,"")

Drag the 3 of them all the way down accordingly to your need. You can then copy the G:J range and paste any time you need next to itself; just change the year in the top left cell and it should do the trick.

Report any question you have or bug you have encountered. If, according to your judgment, this answer (or any other) is the best solution to your problem you have the privilege to accept it (link).