narendra

Board Regular
Joined
Apr 15, 2008
Messages
92
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.

Single List of Dates.xlsx
ABCDEFGH
1(i) Loan 'Installment' dates :Merged List of Dates 'populated'based on thetwo Payment Cycles and the list of fixed payment dates
2Enter Loan Date : 29-Feb-16
3Select Loan Tenure : 9Qtr← = Tenure of : 2 Yr 3 MnthsRemarksDate
4Select Installment Frequency : QtrlyLoan Disbursed29-Feb-16
53Add'l payment Date - Fixed4-Apr-16← Non-cyclic payment as per Table in (iii)
6Installment Due29-May-16
7Installment Due29-Aug-16
8(ii) Dates for 'Additional Payments'Add'l payment Date - Fixed30-Aug-16← Non-cyclic payment as per Table in (iii)
9 to be madeAdd'l payment Date - by Freq31-Aug-16← 1st Additional Payment as per freqency from (ii)
10 WITHIN the Loan TenureAdd'l payment Date - by Freq30-Sep-16
11 as per below frequency :Add'l payment Date - by Freq31-Oct-16
12Installment Due29-Nov-16
13Day (date) of Payments : MonthEndSelect betw.: 1 to 30 or MonthEndAdd'l payment Date - by Freq30-Nov-16
14Begin Payment From : AugSelect the MonthAdd'l payment Date - by Freq31-Dec-16
152016Enter Year (data validation is applied)Add'l payment Date - by Freq31-Jan-17
16Payment Frequency : MonthlySelect: Monthly, Qtrly, Half-Yrly, YearlyInstallment Due28-Feb-17← this is also an 'Additional payment' date as per Freq set in (ii)
171Add'l payment Date - by Freq31-Mar-17
18Add'l payment Date - by Freq30-Apr-17
19Installment Due29-May-17
20(iii) Dates for 'Additional Payments'Add'l payment Date - by Freq31-May-17
21 made on fixed DatesAdd'l payment Date - Fixed12-Jun-17← Non-cyclic payment as per Table in (iii)
22Source of Funds for makingAdd'l payment Date - by Freq30-Jun-17
23Additional PaymentPay DatesAdd'l payment Date - by Freq31-Jul-17
24Commission received4-Apr-16Add'l payment Date - Fixed2-Aug-17← Non-cyclic payment as per Table in (iii)
25Sale of car30-Aug-16Installment Due29-Aug-17← Also a date for Non-cyclic payment as per table in (iii)
26Sale of investments12-Jun-17Add'l payment Date - by Freq31-Aug-17
27Term-Deposit Maturity02-Aug-17Add'l payment Date - by Freq30-Sep-17
28Bonus Received29-Aug-17Add'l payment Date - by Freq31-Oct-17
29To be paid from my savings30-Nov-17Installment Due29-Nov-17
30To be paid from my savings31-Jan-18Add'l payment Date - by Freq30-Nov-17← Also a date for Non-cyclic payment as per table in (iii)
31From Commission receivable30-May-18to be ignored —out of date range as per (i)Add'l payment Date - by Freq31-Dec-17
32From Bonus Receivable02-Aug-18to be ignored —out of date range as per (i)Add'l payment Date - by Freq31-Jan-18← Also a date for Non-cyclic payment as per table in (iii)
33Installment Due28-Feb-18← this is also an Additional payment date (per ii & iii)
34Add'l payment Date - by Freq31-Mar-18
35Add'l payment Date - by Freq30-Apr-18
36Installment Due29-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
RangeFormula
D3D3="← = 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,B17B5=IF(B4="Monthly",1,IF(B4="Qtrly",3,IF(B4="Half-Yrly",6,IF(B4="Yearly",12,"-"))))
G4G4=$B$2
G5G5=B24
G6G6=IF( AND(MONTH(G4)=2, DAY(G4)<>DAY(G$4)), EOMONTH(G4,B$5-1) + DAY(G$4), EDATE(G4,B$5))
G7G7=IF( AND(MONTH(G6)=2, DAY(G6)<>DAY(G$4)), EOMONTH(G6,B$5-1) + DAY(G$4), EDATE(G6,B$5))
G8G8=B25
G9G9=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:G11G10=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) ) )
G12G12=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,G20G13=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,G29G16=IF( AND(MONTH(G12)=2, DAY(G12)<>DAY(G$4)), EOMONTH(G12,B$5-1) + DAY(G$4), EDATE(G12,B$5))
G19,G36G19=IF( AND(MONTH(G16)=2, DAY(G16)<>DAY(G$4)), EOMONTH(G16,B$5-1) + DAY(G$4), EDATE(G16,B$5))
G21G21=B26
G24G24=B27
G25G25=IF( AND(MONTH(G19)=2, DAY(G19)<>DAY(G$4)), EOMONTH(G19,B$5-1) + DAY(G$4), EDATE(G19,B$5))
G26G26=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
CellAllowCriteria
B4ListMonthly,Qtrly,Half-Yrly,Yearly
B3Whole number>0
C3ListYears,Months,Qtr,Half-Yrs
B13ListMonthEnd,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
B14ListJan,Feb,Mar,Apr,Jun,Jul,Aug,Sep,Oct,Nov,Dec
B15Custom=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 )
B16ListMonthly,Qtrly,Half-Yrly,Yearly
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,772
Messages
5,766,387
Members
425,350
Latest member
procha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top