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")))))

Lease EXP	Days	Result
4/30/18	-361	MTM
7/31/19	95	8-2019
2/29/20	308	3-2020
12/31/19	248	1-2019

<tbody>
</tbody>

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
1	Lease EXP	Days	Result
2	4/30/2018	-361	MTM
3	7/31/2019	96	8-2019
4	2/29/2020	309	3-2020
5	12/31/2019	249	1-2020

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula B2 =INT(A2)-INT(TODAY()) C2 =IF(TODAY()>=A2,"MTM",EOMONTH(A2,1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[dave5770]

Excel 2010

	A	B	C
1	Lease EXP	Days	Result
2	4/30/2018	-361	MTM
3	7/31/2019	96	8-2019
4	2/29/2020	309	3-2020
5	12/31/2019	249	1-2020

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula B2 =INT(A2)-INT(TODAY()) C2 =IF(TODAY()>=A2,"MTM",EOMONTH(A2,1)) <tbody> </tbody>

<tbody>
</tbody>

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
1	Lease EXP	Days	Result
2	4/30/2018	-361	MTM
3	7/31/2019	96	8-2019
4	2/29/2020	309	3-2020
5	12/31/2019	249	1-2020

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

SpellCheck​

Worksheet Formulas Cell Formula B2 =INT(A2-TODAY()) C2 =IF(TODAY()>=A2,"MTM",MONTH(EOMONTH(A2,1))&"-"&YEAR(EOMONTH(A2,1))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>