Forum Discussion

ahmerac's avatar
ahmerac
Copper Contributor
Dec 28, 2024
Solved

Transform multiple date ranges into continuous dates list through Dynamic Array

Hi Excel experts, I have data in a table format with StartDate, EndDate, and Amount columns. The data needs to be transformed into an output format that consists of Date and Amount Per Day columns, ...
BI & Data Analysis
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Dec 31, 2024

    =DROP(REDUCE("",SEQUENCE(ROWS(Input)),LAMBDA(u,v,
    LET(
    _days,INDEX(Input,v,2)-INDEX(Input,v,1)+1,
    _seq,SEQUENCE(_days,1,INDEX(Input,v,1)),
    _amt,EXPAND(INDEX(Input,v,3)/_days,_days,1,INDEX(Input,v,3)/_days),
    VSTACK(u,HSTACK(_seq,_amt))))),1)

     

    This returns the expected result in my Excel for the web sheet. The name of the input table is "Input".

     

    Can you share your M-code that achieves the same result?

     

Resources