Forum Discussion

James_Buist's avatar
James_Buist
Brass Contributor
Oct 11, 2024

Spilled Array based on different Time Periods and applied periods - Help Needed

I have another issue that I can't solve. Most info is in the attached example sheet I have a set of time periods start and end dates in rows across the top running from a few historic years to a few...
excel
Formulas and Functions
office 365
djclements's avatar
djclements
Silver Contributor
Oct 12, 2024

James_Buist Give this a try and see if it's what you're looking for...

 

=LET(
    colData, G2:R3,
    rowData, B5:D12,
    rowId, SEQUENCE(ROWS(rowData)),
    start, INDEX(rowData,,2),
    end, INDEX(rowData,,3),
    num, INDEX(rowData,,1),
    low, MIN(start),
    seq, SEQUENCE(,MAX(end)-low+1,low),
    dates1, DATE(YEAR(seq),MONTH(seq),DAY(start)),
    dates2, EOMONTH(seq,0),
    test, NOT(MOD(MONTH(seq)-MONTH(start)+(YEAR(seq)-YEAR(start))*12,num))*(seq=IF(dates1<dates2,dates1,dates2))*(seq>=start)*(seq<=end),
    ids, TOROW(IFS(test,rowId),2),
    dates, TOCOL(IFS(test,seq),2),
    --(MMULT(--(ids=rowId),(dates>=TAKE(colData,1))*(dates<=DROP(colData,1)))>0)
)

 

It's virtually identical to my solution on another thread, with the additional MMULT formula used to flag the applicable periods.

 

Reference: Generating a list of Dates using nested array inside SEQUENCE function 

Resources