Forum Discussion
How to breakdown payments into single record for each month by Excel formula
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.
Thank for your response. I have got a formula from techcommunity and that works fine.
okay. thanks Harun24HR , please close the thread
