Trimester/Semester/Yearly Formula

[munoz83]

Hi,

I'm working on a personal file to handle my insurance portfolio and I need to identify which ones have to pay depending on what they bought ( By trimester,semester or yearly).
The tricky part is that the start date has to remain the same.
i.e. Start date : Nov 1st 2017
Type: Trimester
First payment: Feb 1st 2018
Second Payment: May 1st 2018...... and so on.

Hope someone can help me , thank you!!

 

[actjfc]

Munoz83,

For Trimesters you may use for the first due date:

=DATE(IF(MONTH($I$4)+3>12,YEAR($I$4)+1,YEAR($I$4)),IF(MONTH($I$4)+3>12,MONTH($I$4)+3-12,MONTH($I$4)+3),DAY($I$4))

$I$4 has 11/1/2017, for the second due date use +6 instead of +3 in the formula, for the third due date use +9, for next year use +12.

For Semester use +6
For Yearly use +12, this formula can be simplified, but it works as is.

Test it, please. I hope it helps!

 

[footoo]

Put your start dates in column A
Put in column B the payment month intervals : 3 for trimester, 6 for semester, 12 for yearly
Put in column C the payment number

Then : =EDATE(A2,B2*C2)

 

[jtakw]

Hi,

Enter 1st Due Date in Column B, Column C formulas copied across, if your setup is vertical, adjust formula to suit:

Book1
	A	B	C	D	E	F	G	H	I	J
1		1st Due Date
2	Trimester	11/1/2017	2/1/2018	5/1/2018	8/1/2018	11/1/2018	2/1/2019	5/1/2019	8/1/2019	11/1/2019
3	Semester	12/1/2017	6/1/2018	12/1/2018	6/1/2019	12/1/2019	6/1/2020	12/1/2020	6/1/2021	12/1/2021
4	Yearly	1/1/2018	1/1/2019	1/1/2020	1/1/2021	1/1/2022	1/1/2023	1/1/2024	1/1/2025	1/1/2026
Sheet353

Cell Formulas
Range	Formula
C2	=EDATE(B2,3)
C3	=EDATE(B3,6)
C4	=EDATE(B4,12)

 

[actjfc]

Really nice shorter solutions! Thanks!