Can we build a single formula to auto-populate the list of dates as shown in column 'G', based on the payment cycles set in (i) & (ii) and the list of dates provided in (iii), without repeating common dates between them?
For now, I prepared separate list of dates using formulas to calculate each payment cycle from (i) and (ii), and then manually combined them along with list of dates from table (iii) in a chronological order (removing duplicate dates). The desired result is put together in column 'G' for illustration.
For now, I prepared separate list of dates using formulas to calculate each payment cycle from (i) and (ii), and then manually combined them along with list of dates from table (iii) in a chronological order (removing duplicate dates). The desired result is put together in column 'G' for illustration.
| Single List of Dates.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | (i) Loan 'Installment' dates : | Merged List of Dates 'populated' | based on the | two Payment Cycles and the list of fixed payment dates | ||||||
| 2 | Enter Loan Date : | 29-Feb-16 | ||||||||
| 3 | Select Loan Tenure : | 9 | Qtr | ← = Tenure of : 2 Yr 3 Mnths | Remarks | Date | ||||
| 4 | Select Installment Frequency : | Qtrly | Loan Disbursed | 29-Feb-16 | ||||||
| 5 | 3 | Add'l payment Date - Fixed | 4-Apr-16 | ← Non-cyclic payment as per Table in (iii) | ||||||
| 6 | Installment Due | 29-May-16 | ||||||||
| 7 | Installment Due | 29-Aug-16 | ||||||||
| 8 | (ii) Dates for 'Additional Payments' | Add'l payment Date - Fixed | 30-Aug-16 | ← Non-cyclic payment as per Table in (iii) | ||||||
| 9 | to be made | Add'l payment Date - by Freq | 31-Aug-16 | ← 1st Additional Payment as per freqency from (ii) | ||||||
| 10 | WITHIN the Loan Tenure | Add'l payment Date - by Freq | 30-Sep-16 | |||||||
| 11 | as per below frequency : | Add'l payment Date - by Freq | 31-Oct-16 | |||||||
| 12 | Installment Due | 29-Nov-16 | ||||||||
| 13 | Day (date) of Payments : | MonthEnd | ← | Select betw.: 1 to 30 or MonthEnd | Add'l payment Date - by Freq | 30-Nov-16 | ||||
| 14 | Begin Payment From : | Aug | ← | Select the Month | Add'l payment Date - by Freq | 31-Dec-16 | ||||
| 15 | 2016 | ← | Enter Year (data validation is applied) | Add'l payment Date - by Freq | 31-Jan-17 | |||||
| 16 | Payment Frequency : | Monthly | ← | Select: Monthly, Qtrly, Half-Yrly, Yearly | Installment Due | 28-Feb-17 | ← this is also an 'Additional payment' date as per Freq set in (ii) | |||
| 17 | 1 | Add'l payment Date - by Freq | 31-Mar-17 | |||||||
| 18 | Add'l payment Date - by Freq | 30-Apr-17 | ||||||||
| 19 | Installment Due | 29-May-17 | ||||||||
| 20 | (iii) Dates for 'Additional Payments' | Add'l payment Date - by Freq | 31-May-17 | |||||||
| 21 | made on fixed Dates | Add'l payment Date - Fixed | 12-Jun-17 | ← Non-cyclic payment as per Table in (iii) | ||||||
| 22 | Source of Funds for making | Add'l payment Date - by Freq | 30-Jun-17 | |||||||
| 23 | Additional Payment | Pay Dates | Add'l payment Date - by Freq | 31-Jul-17 | ||||||
| 24 | Commission received | 4-Apr-16 | Add'l payment Date - Fixed | 2-Aug-17 | ← Non-cyclic payment as per Table in (iii) | |||||
| 25 | Sale of car | 30-Aug-16 | Installment Due | 29-Aug-17 | ← Also a date for Non-cyclic payment as per table in (iii) | |||||
| 26 | Sale of investments | 12-Jun-17 | Add'l payment Date - by Freq | 31-Aug-17 | ||||||
| 27 | Term-Deposit Maturity | 02-Aug-17 | Add'l payment Date - by Freq | 30-Sep-17 | ||||||
| 28 | Bonus Received | 29-Aug-17 | Add'l payment Date - by Freq | 31-Oct-17 | ||||||
| 29 | To be paid from my savings | 30-Nov-17 | Installment Due | 29-Nov-17 | ||||||
| 30 | To be paid from my savings | 31-Jan-18 | Add'l payment Date - by Freq | 30-Nov-17 | ← Also a date for Non-cyclic payment as per table in (iii) | |||||
| 31 | From Commission receivable | 30-May-18 | ← | to be ignored —out of date range as per (i) | Add'l payment Date - by Freq | 31-Dec-17 | ||||
| 32 | From Bonus Receivable | 02-Aug-18 | ← | to be ignored —out of date range as per (i) | Add'l payment Date - by Freq | 31-Jan-18 | ← Also a date for Non-cyclic payment as per table in (iii) | |||
| 33 | Installment Due | 28-Feb-18 | ← this is also an Additional payment date (per ii & iii) | |||||||
| 34 | Add'l payment Date - by Freq | 31-Mar-18 | ||||||||
| 35 | Add'l payment Date - by Freq | 30-Apr-18 | ||||||||
| 36 | Installment Due | 29-May-18 | ||||||||
| 37 | ←should ignore (return blank or error) for 30-May-18 from table iii | |||||||||
| 38 | ←should ignore (return blank or error) for 02-Aug-18 from table iii | |||||||||
| 39 | .. | ←return blank/error here onwards (no more dates to populate) | ||||||||
| 40 | .. | |||||||||
Sheet1 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D3 | D3 | ="← = Tenure of : "&IF(C3="Months",INT(B3/12)&" Yr "&MOD(B3,12)&" Mnths",IF(C3="Qtr",INT(B3/4)&" Yr "&(MOD(B3,4)*3)&" Mnths",IF(C3="Half-Yrs",INT(B3/2)&" Yr "&(MOD(B3,2)*6)&" Mnths",IF(C3="Years",B3&" "&C3,"-")))) |
| B5,B17 | B5 | =IF(B4="Monthly",1,IF(B4="Qtrly",3,IF(B4="Half-Yrly",6,IF(B4="Yearly",12,"-")))) |
| G4 | G4 | =$B$2 |
| G5 | G5 | =B24 |
| G6 | G6 | =IF( AND(MONTH(G4)=2, DAY(G4)<>DAY(G$4)), EOMONTH(G4,B$5-1) + DAY(G$4), EDATE(G4,B$5)) |
| G7 | G7 | =IF( AND(MONTH(G6)=2, DAY(G6)<>DAY(G$4)), EOMONTH(G6,B$5-1) + DAY(G$4), EDATE(G6,B$5)) |
| G8 | G8 | =B25 |
| G9 | G9 | =IF( B13="MonthEnd", EOMONTH(--(B14&"-"&B15),0), IF(B14="Feb", MIN( B13, DAY(EOMONTH(--(B14&"-"&B15),0)) ), --(B13&"-"&B14&"-"&B15)) ) |
| G34:G35,G31:G32,G27:G28,G23,G17:G18,G14:G15,G10:G11 | G10 | =IF( $B$13="MonthEnd", EOMONTH(G9+1,$B$17-1), IF( (MONTH(EDATE(G9,$B$17)) - MONTH(G9)) <> $B$17, EOMONTH( DATE(YEAR(G9),MONTH(G9)+$B$17,1), 0), EDATE(G9,$B$17) ) ) |
| G12 | G12 | =IF( AND(MONTH(G7)=2, DAY(G7)<>DAY(G$4)), EOMONTH(G7,B$5-1) + DAY(G$4), EDATE(G7,B$5)) |
| G13,G30,G22,G20 | G13 | =IF( $B$13="MonthEnd", EOMONTH(G11+1,$B$17-1), IF( (MONTH(EDATE(G11,$B$17)) - MONTH(G11)) <> $B$17, EOMONTH( DATE(YEAR(G11),MONTH(G11)+$B$17,1), 0), EDATE(G11,$B$17) ) ) |
| G16,G33,G29 | G16 | =IF( AND(MONTH(G12)=2, DAY(G12)<>DAY(G$4)), EOMONTH(G12,B$5-1) + DAY(G$4), EDATE(G12,B$5)) |
| G19,G36 | G19 | =IF( AND(MONTH(G16)=2, DAY(G16)<>DAY(G$4)), EOMONTH(G16,B$5-1) + DAY(G$4), EDATE(G16,B$5)) |
| G21 | G21 | =B26 |
| G24 | G24 | =B27 |
| G25 | G25 | =IF( AND(MONTH(G19)=2, DAY(G19)<>DAY(G$4)), EOMONTH(G19,B$5-1) + DAY(G$4), EDATE(G19,B$5)) |
| G26 | G26 | =IF( $B$13="MonthEnd", EOMONTH(G23+1,$B$17-1), IF( (MONTH(EDATE(G23,$B$17)) - MONTH(G23)) <> $B$17, EOMONTH( DATE(YEAR(G23),MONTH(G23)+$B$17,1), 0), EDATE(G23,$B$17) ) ) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B4 | List | Monthly,Qtrly,Half-Yrly,Yearly |
| B3 | Whole number | >0 |
| C3 | List | Years,Months,Qtr,Half-Yrs |
| B13 | List | MonthEnd,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31 |
| B14 | List | Jan,Feb,Mar,Apr,Jun,Jul,Aug,Sep,Oct,Nov,Dec |
| B15 | Custom | =AND(MOD(B15,1)=0, IF(B13="MonthEnd",EOMONTH(--(B14&"-"&B15),0),IF(B14="Feb",MIN(B13,DAY(EOMONTH(--(B14&"-"&B15),0))),--(B13&"-"&B14&"-"&B15))) >B2 ) |
| B16 | List | Monthly,Qtrly,Half-Yrly,Yearly |
