Forum Discussion
Excel Date calculation formula
Good Morning, I'm new to this forum. I have an intermediate knowledge of Excel (Mac Office 365) and need help with a date formula. Basically, it goes like this: I have to create a payment schedule. I...
Please below is the formula for the Due Dates using RandArray function.
For the Due Dates column as date
| Deposit | Due Date | Amount | Total Amount | Deposit | Rate | ||
| 1st | 44762 | =SUM(INDEX($H$2:$H$7,MATCH(A2,$G$2:$G$7,0))*$E$2) | 50000 | 1st | 0.2 | ||
| 2nd | =SORT(RANDARRAY(COUNTA(A3:A6),1,B2,B7,TRUE)) | =SUM(INDEX($H$2:$H$7,MATCH(A3,$G$2:$G$7,0))*$E$2) | 2nd | 0.3 | |||
| 3rd | =SUM(INDEX($H$2:$H$7,MATCH(A4,$G$2:$G$7,0))*$E$2) | 3rd | 0.25 | ||||
| 4th | =SUM(INDEX($H$2:$H$7,MATCH(A5,$G$2:$G$7,0))*$E$2) | 4th | 0.1 | ||||
| 5th | =SUM(INDEX($H$2:$H$7,MATCH(A6,$G$2:$G$7,0))*$E$2) | 5th | 0.1 | ||||
| Final | 45493 | =SUM(INDEX($H$2:$H$7,MATCH(A7,$G$2:$G$7,0))*$E$2) | Final | =$H$8-SUM(H2:H6) | |||
| =SUM(C2:C7) | 1 |
| Deposit | Due Date | Amount | Total Amount | Deposit | Rate | ||
| 1st | 7/20/2022 | $10,000.00 | $50,000.00 | 1st | 20% | ||
| 2nd | 9/8/2022 | $15,000.00 | 2nd | 30% | |||
| 3rd | 4/20/2023 | $12,500.00 | 3rd | 25% | |||
| 4th | 8/7/2023 | $5,000.00 | 4th | 10% | |||
| 5th | 5/12/2024 | $5,000.00 | 5th | 10% | |||
| Final | 7/20/2024 | $2,500.00 | Final | 5% | |||
| $50,000.00 | 100% |
This is great, so helpful, thank you so much!! Very generous response, most grateful!