Recurring rent dates formula

[Excel_1996_]

Hi,

The company I work for has several properties that are rented out, some pay bi weekly some pay monthly. What I would like to know is, is there any way I can set a condition or formula to say that if in column A the first rent is due on 'X' date, then in column B the next due date is 'X + 14' But then have this automatically update rather than manually update the date every 2 weeks?

 

[Micron]

One way might be to have B1 as =A1+14 and use the wb open event to check the value of A1, which contains the 'seed' date value. If the current date is greater than the value of B1, the code in the event updates A1 to the value of B1 formula result. The possible problems with that is,
- the wb must be opened for anything to happen
- it assumes that the rent was paid on the due date
You could also just drag the formula down and get a list of due dates.

 

[Excel_1996_]

One way might be to have B1 as =A1+14 and use the wb open event to check the value of A1, which contains the 'seed' date value. If the current date is greater than the value of B1, the code in the event updates A1 to the value of B1 formula result. The possible problems with that is,
- the wb must be opened for anything to happen
- it assumes that the rent was paid on the due date
You could also just drag the formula down and get a list of due dates.

Hi Micron,

That could work, I'm happy for the excel to assume rents are paid on time.

Only thing is I am a total newbie to excel and don't know how to set the open work book to check the value of A2.

The company have a LOT of rental properties so I am trying to save space and avoid dragging down due date formulas!

 

[Micron]

Open the vb editor (alt+F11). Drop down the objects list from near the top left. Dbl click on ThisWorkbook; a code page will open. One drop down above that should say General. Drop that down, choose Workbook. Find Open event in the drop down to the right & choose that. You will start a code block for the Open event.
Google to find code that will check your cell when that event runs, which will be each time you open the wb.

 

[Micron]

You could try pasting this simple version into the code after you start the open event code as described above. See if it works but change to your sheet name and range values. Play with a copy of your wb rather than your production wb. Your formula would be like =B2+14 (if the seed date is in B2) in whatever cell you want to be updated. If you have worksheet_change code for your sheet in your wb, then you might want to disable running it just when the wb is opened.

With Sheets("7")
     If .Range("B2") <= Date Then .Range("B2") = Date
End With