Calculating the Months Remaining of a Contract from Today's Date ETF

[rim261]

I'm trying to see how many months are remaining from today's date pertaining a certain number of months in a contract with a price of an early termination fee.

I receive the date as this in excel in column B : 2/7/2018 20:07. I have the number of months as 48 in the contract, for this example, in column D. The price of monthly pay for ETF would be $8 in column C.

I need to calculate how much they would pay from C by having the number of months D from today's date subtracted from the original start date B. I'm new to this so I'm not sure if I explained it correctly.

 

[rim261]

Sorry, I just realized I don't need the ETF part added. Only everything calculating the months remaining of the contract from today's date to the last date of the original date

 

[Dave Patton]

Excel 2010
	B	C	D	E	F
1	Start		Months	Today	Months to End of Contract
2	2-Jul-18		48	26-Oct-18	44
3
3b

Cell Formulas
Range	Formula
F2	=DATEDIF(E2,DATE(YEAR(B2),MONTH(B2)+D2,DAY(B2)),"m")

 

[rim261]

Excel 2010

	B	C	D	E	F
Start	Months	Today	Months to End of Contract

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2-Jul-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]26-Oct-18[/TD]
[TD="align: right"]44
[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

3b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=DATEDIF(E2,DATE(YEAR(B2),MONTH(B2)+D2,DAY(B2)),"m")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

That did work if I modified it for that example so I took out the time in one cell. The thing is, I get these in bulks (10's, 50's, or 100's of them). The date that I receive includes the time in it; such as "2/7/2018 20:07". Also, I don't have a column for today's date that comes in the spreadsheet. Is there a way to do that without "E2"? But still thanks for your answer, it's an excellent direction to go in.

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="width: 99, align: center"]Start_Date[/TD]

</tbody>

[/TD]
[TD="align: center"][TABLE="width: 155"]
<colgroup><col width="155"></colgroup><tbody>[TR]
[TD="class: xl65, width: 155"]Original Contract Terms[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

​

<colgroup><col width="84"></colgroup><tbody>
[TD="class: xl65, width: 84"]Fee[/TD]

</tbody>

[/TD]
[TD][/TD]
[TD]

​

<colgroup><col width="161"></colgroup><tbody>
[TD="class: xl65, width: 161"]Months Remaining (ETF)[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD="align: center"]2/7/2018 20:07
[/TD]
[TD="align: center"]60
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]?
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

The time should not change the result.

Excel 2010
	B	C	D	E	F
1	Start		Months		Months to End of Contract
3	2-Jul-18		48		44
4
3b

Cell Formulas
Range	Formula
F3	=DATEDIF(TODAY(),DATE(YEAR(B3),MONTH(B3)+D3,DAY(B3)),"m")

 

[rim261]

The time should not change the result.

Excel 2010

	B	C	D	E	F
Start	Months	Months to End of Contract

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2-Jul-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"]44[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

3b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=DATEDIF(TODAY(),DATE(YEAR(B3),MONTH(B3)+D3,DAY(B3)),"m")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Awesome! Works perfectly! Thanks a bunch!