Forum Discussion

Harun24HR's avatar
Harun24HR
Bronze Contributor
Sep 24, 2025

How to breakdown payments into single record for each month by Excel formula

I have few donors who will donate for different duration. I want to break down that duration into single records for each month. Attach is my sample input and desired output. I wish to do this by Excel formulas only.

 

community

3 Replies

  • Surya_Narayana's avatar
    Surya_Narayana
    MCT
    Sep 25, 2025

    hi Harun24HR​  you can try below and let me know the outcome

     

    Example Input (Sheet1)

    ID

    Name

    Donation Amount

    Start Date

    End Date

    Status

    1

    Alex

    100

    01-Jan-25

    01-Apr-25

    Active

    2

    Maria

    200

    01-Feb-25

    01-May-25

    Pending

     

    Desired Output (Sheet2)

    ID

    Name

    Donation Amount

    Donation Date

    Status

    1

    Alex

    100

    01-Jan-25

    Active

    2

    Maria

    200

    01-Feb-25

    Pending

    2

    Maria

    200

    01-Mar-25

    Pending

     

    Formula Approach

    Helper Column: Number of Months (in Sheet1, column G for example):

    =DATEDIF(D2,E2,"m")

    This gives the number of months between Start and End.

    In Output Sheet (Sheet2), set up the rows using SEQUENCE + INDEX (Excel 365 required):

    Donation Date (col D)

    =EOMONTH(INDEX(Sheet1!$D:$D,INT((ROW(A1)-1)/1)+1),MOD(ROW(A1)-1,Sheet1!$G$2+1))

    This formula will generate each month between Start and End.

    ID, Name, Donation Amount, Status
    Use INDEX() to pull values from input table. Example for ID:

    =INDEX(Sheet1!$A:$A,INT((ROW(A1)-1)/1)+1)

    Drag formulas down far enough to cover the maximum months across donors.

Resources