Calculating a Due date with Different frequency

[lallens]

I need a formula that can tell me the next due date if I have the first due date, the frequency the payment is being paid (Monthly, Weekly, Semi-monthly) and the number of payments that are setup.

Column D is the first Payment date, Column E is the frequency, Column F is the number of payments, Column G is were I would put the next due date.

So if I have a person paying $xx amount first payment is due 11/1/2018 and they are paying weekly for 10 payments. Column G should give me a date of 11/22/2018 since it is greater than today date and still within the 10 payments.

Thanks in advance
Allen

 

[RasGhul]

Can you post some sample data so we can look at a solution?

 

[lallens]

I sure can and thanks for the assistance..

Account#	Account#	Pmt Amt	Start Date	Frequency	# of Pmts	Current Pmt Date	Pmt plan ends
11555226	4001111	$125.00	07/01/2018	MON	7	<<What I need>>	1/1/2019

<tbody>
</tbody>

PMT= payment(s)

So in theory what I should have in Column G (Current Pmt Date) should be 12/01/2018 since we have an arrangement that started on 7/1/2018 paying 7 payments. I can add a TODAY() somewhere if I need to.

Allen

 

[Yongle]

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	Account#	Account#	Pmt Amt	Start Date	Frequency	# of Pmts	Current Pmt Date	Pmt plan ends	long formula without naamed ranges
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​				07/01/2018​	S		07/12/2018​	01/01/2019​	07/12/2018​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​				08/01/2018​	M		08/12/2018​	02/01/2019​	08/12/2018​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​				09/01/2018​	W		27/11/2018​	03/01/2019​	27/11/2018​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​				10/01/2018​	W		Ended	30/05/2018​	Ended
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​				11/01/2018​	S		26/11/2018​	05/01/2019​	26/11/2018​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	=IF(H2<today(),"ended",if(e2="w",weekly,if(e2="m",monthly,if(e2="s",semi,"freq?"))))< div=""></today(),"ended",if(e2="w",weekly,if(e2="m",monthly,if(e2="s",semi,"freq?"))))<>​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

=IF(H2<today(),"ended",if(e2="w",$d2+int((today()+7-$d2) 7)*7,if(e2="M" ,day($d2)+if(day($d2)<day(today()),eomonth(today(),0),eomonth(today(),-1)),if(e2="S" ,if(day($d2)+if(day($d2)<day(today()),eomonth(today(),0),eomonth(today(),-1))-today()="">15,DAY($D2)+IF(DAY($D2)<day(today()),eomonth(today(),0),eomonth(today(),-1))-15,day($d2)+if(day($d2)<day(today()),eomonth(today(),0),eomonth(today(),-1))),"freq?"))))< div=""></day(today()),eomonth(today(),0),eomonth(today(),-1))-15,day($d2)+if(day($d2)<day(today()),eomonth(today(),0),eomonth(today(),-1))),"freq?"))))<></today(),"ended",if(e2="w",$d2+int((today()+7-$d2)>​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

 

[Yongle]

Ignore that post! posting problems!!!

 

[Yongle]

Hopefully this posting attempt works
(the problem was caused by forum security rejecting Less Than symbol and removing rest of formula - so I amended formula to use Greater Than instead)

In G2 copy down
=IF(TODAY()>H2,"Ended",IF(E2="W",'Sheet Name'!$D2+INT((TODAY()+7-'Sheet Name'!$D2)/7)*7,IF(E2="M",DAY('Sheet Name'!$D2)+EOMONTH(TODAY(),-(DAY('Sheet Name'!$D2)>=DAY(TODAY()))),IF(E2="S",DAY('Sheet Name'!$D2)+EOMONTH(TODAY(),-(DAY('Sheet Name'!$D2)>=DAY(TODAY())))-(DAY('Sheet Name'!$D2)+EOMONTH(TODAY(),-(DAY('Sheet Name'!$D2)>=DAY(TODAY())))-TODAY()>15)*15,"Freq?"))))

Results I get

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Account#	Account#	Pmt Amt	Start Date	Frequency​	# of Pmts	Current Pmt Date​	Pmt plan ends​
2​				07/01/2018​	M​		07/12/2018​	01/01/2019​
3​				08/01/2018​	M​		08/12/2018​	02/01/2019​
4​				09/01/2018​	M​		09/12/2018​	03/01/2019​
5​				10/01/2018​	S​		Ended​	30/05/2018​
6​				11/01/2018​	​		Freq?​	05/01/2019​
7​				12/01/2018​	S​		27/11/2018​	06/01/2019​
8​				13/01/2018​	S​		28/11/2018​	07/01/2019​
9​				14/01/2018​	W​		25/11/2018​	08/01/2019​
10​				15/01/2018​	W​		26/11/2018​	09/01/2019​
11​				16/01/2018​	S​		01/12/2018​	10/01/2019​
12​				17/01/2018​	S​		02/12/2018​	11/01/2019​
13​				18/01/2018​	S​		Ended​	21/11/2018​
14​				19/01/2018​	S​		Ended​	22/11/2018​
15​				20/01/2018​	S​		05/12/2018​	23/11/2018​
16​				21/01/2018​	S​		06/12/2018​	24/11/2018​
17​				22/01/2018​	S​		07/12/2018​	25/11/2018​
18​				23/01/2018​	S​		23/11/2018​	26/11/2018​
19​				24/01/2018​	S​		24/11/2018​	27/11/2018​
20​				25/01/2018​	S​		25/11/2018​	28/11/2018​
21​				26/01/2018​	M​		26/11/2018​	29/11/2018​
22​				27/01/2018​	M​		27/11/2018​	30/11/2018​
23​				28/01/2018​	M​		28/11/2018​	01/12/2018​
24​				29/01/2018​	S​		29/11/2018​	02/12/2018​
25​				30/01/2018​	S​		30/11/2018​	03/12/2018​
26​				31/01/2018​	S​		01/12/2018​	04/12/2018​
27​				01/02/2018​	W​		29/11/2018​	05/12/2018​
28​				02/02/2018​	W​		30/11/2018​	06/12/2018​
29​				03/02/2018​	W​		24/11/2018​	07/12/2018​
30​				04/02/2018​	S​		04/12/2018​	08/12/2018​
31​				05/02/2018​	S​		05/12/2018​	09/12/2018​
32​				06/02/2018​	S​		06/12/2018​	10/12/2018​
33​				07/02/2018​	S​		07/12/2018​	11/12/2018​
34​				08/02/2018​	M​		08/12/2018​	12/12/2018​
35​				09/02/2018​	M​		09/12/2018​	13/12/2018​

Sheet: Sheet Name

 

[Yongle]

The above formula makes use of the Date of Last payment (and ignores No# of payments)
Results are based on today is 23 November 2018

There is a way to achieve this using 3 Named Ranges and the formula is then reduced to
=IF(TODAY()>H2,"Ended",IF(E2="W",Weekly,IF(E2="M",Monthly,IF(E2="S",Semi,"Freq?"))))

If the original formula gives you what you want, I will provide you with the formulas for the 3 Named ranges
- I will need to the name of the worksheet

 

[lallens]

The sheet name is payments which will hold the formulas and the columns for the reps to fill out. I will lock the cells with the formulas so nothing happens (And of course keep a master copy just in case).

 

[Yongle]

Did the formula return what you wanted?

 

[lallens]

I think there may be a problem with the weekly.. I have a payment date of 11/2/2018 Weekly Frequency and 3 payments it is showing me the next payment of 11/30/2018 when it should be 11/16/2018 or Ended