Date rent increase occurs

[Sandy44830]

Rent increase occurs June 1, 2021 unless the current lease end date is greater than June 1, 2021. If that is the case the lot increase would be the lease end date plus 30 days and how ever many days to the first of the next month (lease end date is July 30, 2021 so increase would go into effect August 1, 2021).

Thank you for your consideration

 

[jtakw]

Hi,

May be something like this:

Book3.xlsx
	A	B
1	Lease End Date	Increase Date
2	5/31/2021	6/1/2021
3	6/30/2021	7/1/2021
4	2/28/2021	6/1/2021
5	8/15/2021	9/1/2021
Sheet919

Cell Formulas
Range	Formula
B2:B5	B2	=MAX(DATE(YEAR(A2),6,1),EOMONTH(A2,0)+1)

 

[Sandy44830]

Thank you. I did find out that I do not have to add 30 days to the end lease date so with your help from above this is what i have

=IF(G8>DATE(2021,6,1),MAX(DATE(YEAR(G8),6,1),EOMONTH(G8,0)+1),DATE(2021,6,1))

I am still having a problem. Some cells in the lease end column are empty which give me the correct date of June 1, 2021, howver if the date is prior to June 1, 2021 the date in the Increase Date column is a date older than June 1, 2021. Ex: Lease end date: November 18, 2018, Increase Date: Decembr 1, 2018 (this should be June 1, 2021).

 

[jtakw]

I modified my formula accordingly.

My formula should work as-is, you Don't need the IF test, and it'll continue to work for future years:

Book3.xlsx
	A	B
1	Lease End Date	Increase Date
2	5/31/2021	6/1/2021
3	6/30/2021	7/1/2021
4	2/28/2021	6/1/2021
5	8/15/2021	9/1/2021
6		6/1/2021
7	11/18/2018	6/1/2021
Sheet919

Cell Formulas
Range	Formula
B2:B7	B2	=MAX(DATE(YEAR(TODAY()),6,1),EOMONTH(A2,0)+1)

 

[Sandy44830]

Thank you, your formula did work. I should have checked this out before. The data was exported from Rent Manager, the date field is formatted as text. Changing the column to date format does not correct the problem unless I re-enter those dates.

 

[jtakw]

If you have a Long list of dates in the Same column, you can try Text to Columns in the Data Tab to convert the Text dates to Real dates all in one shot.
Or convert them with a formula, then copy & paste value over the original, then delete the formula column:

Book3.xlsx
	H	I	J	K
1	Highlight			Or
2	Select	5/31/2021	5/31/2021	Use formula
3	Column I	6/30/2021	6/30/2021
4	Text to Columns	2/28/2021	2/28/2021
5	Finish	8/15/2021	8/15/2021
6			#VALUE!
7		11/18/2018	11/18/2018
Sheet919

Cell Formulas
Range	Formula
J2:J7	J2	=DATEVALUE(I2)