excelexcel-formulalagtimeline

Consolidated Date window

Consolidated dates

I am attempting to create a formula, that will create a string of consolidated dates by the minimum and maximum, separated by cells containing single digits.

The formula I've been attempting to manipulate is listed below:

=UNIQUE(FILTER(MIN(B1:P1),LEN(B1:P1)>1,""))
Solution

  • You could try using anyone of the following solutions:

    enter image description here

    • Formula used in cell A3

    =LET(
     a, B1:P1, 
     b, IF(a>400,a,"-"), 
     c, TEXTSPLIT(ARRAYTOTEXT(b),,"-"), 
     REDUCE("End Result",c,LAMBDA(x,y,HSTACK(x,
     AGGREGATE({15,14},6,--TEXTSPLIT(y,,", ",1),1)))))

    • Or, Using GROUPBY():

    =LET(
     a, TOCOL(B1:P1),
     b, SCAN(1,--(a<365),SUM)/(a>365),
     HSTACK("End Result",TOROW(DROP(GROUPBY(b, a, 
     HSTACK(MIN,MAX),,0,,ISNUMBER(b)),1,1))))

    • Or:

    =LET(
     a, TOCOL(B1:P1),
     b, SCAN(1,--(a<365),SUM)/(a>365),
     REDUCE("End Result",UNIQUE(TOCOL(b,3)),LAMBDA(x,y,
     HSTACK(x,AGGREGATE({15,14},6,a/(b=y),1)))))