Calculating lease end month/year

[dave5770]

Hi all -

I have a tenant info sheet that contains a lease expiration date. I am attempting to create a formula that either calculates the month and year of the lease end date or results in "MTM" (month-to-month) when the lease expiration is in the past. I have a helper column that calculates the number of days between the lease expiration date and today's date. I created the below formula:

=IF(V30<1,"MTM",IF(V30>1,CONCATENATE(MONTH((U30+1))," - ",(TEXT(U30,"yyyy")))))

[TABLE="width: 500"]
<tbody>[TR]
[TD]Lease EXP
[/TD]
[TD]Days
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]4/30/18
[/TD]
[TD]-361
[/TD]
[TD]MTM
[/TD]
[/TR]
[TR]
[TD]7/31/19
[/TD]
[TD]95
[/TD]
[TD]8-2019
[/TD]
[/TR]
[TR]
[TD]2/29/20
[/TD]
[TD]308
[/TD]
[TD]3-2020
[/TD]
[/TR]
[TR]
[TD]12/31/19
[/TD]
[TD]248
[/TD]
[TD]1-2019
[/TD]
[/TR]
</tbody>[/TABLE]

It works fine unless the lease expiration is in December and then it gives the wrong year. Examples, lease expires on 12/31/19 and the results of the formula is 1-2019 instead of 1-2020. I see the problem in the formula and was trying to figure out another IF component but thought to ask if there was a better way. Maybe someone with more experience knows of a better solution.

 

[CalcSux78]

Excel 2010

	A	B	C
Lease EXP	Days	Result
MTM

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

[TD="align: center"]2[/TD]
[TD="align: right"]4/30/2018[/TD]
[TD="align: right"]-361[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7/31/2019[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]8-2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2/29/2020[/TD]
[TD="align: right"]309[/TD]
[TD="align: right"]3-2020[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]1-2020[/TD]

</tbody>

Sheet3​

[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] "]B2[/TH]
[TD="align: left"]=INT(A2)-INT(TODAY())[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(TODAY()>=A2,"MTM",EOMONTH(A2,1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dave5770]

Excel 2010

	A	B	C
Lease EXP	Days	Result
MTM

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]4/30/2018[/TD]
[TD="align: right"]-361[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7/31/2019[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]8-2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2/29/2020[/TD]
[TD="align: right"]309[/TD]
[TD="align: right"]3-2020[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]1-2020[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=INT(A2)-INT(TODAY())[/TD]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=IF(TODAY()>=A2,"MTM",EOMONTH(A2,1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi and thanks. The only problem with this formula is it returns the last date of the month after the lease expiration and what I need is the number of the month after the lease expiration concatenated with the year of expiration so I can sort or filter all of the lease expirations in a given month.

For example: the lease expires on 11/30/19 and the result of the formula is 12-2019. This is the month the tenant is due a renewal (the month after their lease expires).

 

[CalcSux78]

Excel 2010

	A	B	C
Lease EXP	Days	Result
MTM
8-2019
3-2020
1-2020

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

[TD="align: center"]2[/TD]
[TD="align: right"]4/30/2018[/TD]
[TD="align: right"]-361[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7/31/2019[/TD]
[TD="align: right"]96[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2/29/2020[/TD]
[TD="align: right"]309[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]249[/TD]

</tbody>

SpellCheck​

[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] "]B2[/TH]
[TD="align: left"]=INT(A2-TODAY())[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(TODAY()>=A2,"MTM",MONTH(EOMONTH(A2,1))&"-"&YEAR(EOMONTH(A2,1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]