I want to list all the dates between a list of start and end dates. I have used sequence formula for each row of dates but I am looking for one single formula to work irrespective of any number of rows I have. Any help is highly appreciated

CodePudding user response：

I entered this formula in cell E2

=sort(query(sequence(max(A2:B)-min(A2:B) 1,1,min(A2:B)),
"where "&join(" or ","(Col1>="&filter(A2:A,A2:A<>"")&" 
and Col1<="&filter(B2:B,B2:B<>"")&")")&
" format Col1 'dd.mmm.yyyy'"))

First, we list all the dates from the lowest one to the greatest one in the range A2:B, then we use query to remove all those who aren't between the specified intervals.

CodePudding user response：

While there is already an answer for this, if the same date is repeated in multiple columns, that won't be reflected in the resulting list. Therefore, if you want the dates included in multiple intervals to be repeated, here is an alternative:

=ARRAYFORMULA(QUERY(FLATTEN(IF(DAYS(B2:B4,A2:A4)>=SEQUENCE(1,1000,0),A2:A4 SEQUENCE(1,1000,0),"")),"where Col1 is not null"))

• Use DAYS, SEQUENCE and IF to get a list of all the dates in between the intervals.
• Use FLATTEN to put all values in a single column.
• Use QUERY to remove the null values.