Lease renewal calendar in Excel: which end date is due
A lease renewal calendar is one row per unit you already manage. You type the unit, the building, the tenant, the lease end date, and the notice lead time in days for that lease. The notice date is the end date minus those days. Days left are the end date minus today while the lease is still active or in notice. A window says due when the days left are within the notice lead time you typed, and later when the end is still further out. This sheet is not a real-estate agent's sales list, a viewing diary, or a commission calculator. Those track deals that may never become a lease. Here the lease already exists. The question is which end date is inside the notice window you typed for that row. The notice lead time is a number you type on the row, because it is not the same for every lease. The sheet does not invent a legal period. If the lease says 90 days, you type 90. If another lease says 30, you type 30. Changing the number moves the notice date. You still have to read the lease. Ended and renewed rows drop out of the window. They stay on the sheet so you can see what already finished. The trial lasts 7 days, with no card. Elyx can write the notice date, the days left, and the window. You still type the end date and the notice days.
The Problem
The renewal list fails when it lives in a diary. A note says a lease ends in the spring. Nobody can sort by end date, and nobody can see which row is already inside its own notice window. A sentence without a date is not a calendar. A second failure is mixing units you manage with buyers you are trying to sell to. An agent's pipeline of mandates and viewings answers a different question. If you add offer amounts and viewing dates here, the morning filter no longer means which lease end is due. Keep this sheet to units, end dates, and the notice days you typed. A third failure is deleting a lease when it ends. Set the status to ended or renewed and leave the row. The window goes blank. Deleting the row removes the only record of the end date you already passed.
Benefits
Each unit is one row with an end date and a notice lead time you typed.
The notice date is the end date minus those days.
Days left are the end date minus today while the lease is still live.
Due means days left are within the notice days on that row.
Ended and renewed rows stay on the sheet and drop out of the due count.
Step-by-Step Tutorial
Create the list and the header
Name the sheet Leases. In row 1 type: Unit, Building, Tenant, End, Notice days, Notice date, Status, Days left, Window, Note. Unit is the door or the lot you manage. Building groups units if you have more than one. Tenant is a name. End is a real date. Notice days is a whole number you typed for that lease. Notice date, Days left, and Window are formulas. Status is a word from a list. Note is optional. A rent amount can sit in Note if you need it. It is not what this sheet counts. Freeze row 1. Do not merge cells.
One row per current lease. A renewal that replaces the end date should update End, or become a new row if you need both dates. Do not keep two active rows for one unit.
Lock the status words
Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is active, notice, renewed, and ended. Active means the lease is running and you have not started the notice. Notice means you have sent or received notice and the end date still stands. Renewed means a new end date is agreed: put that new date in End or start a new row, and set this row to renewed if it is the old term. Ended means the unit is no longer on this term. Only active and notice stay in the due window. People must pick a word. A synonym such as finished or gone will not match.
Notice days are not a status. They are a number. Do not type 90 in the status column.
Enter one lease you can check by hand
On row 2 type a unit, a building, a tenant, an end date 20 days after today, and notice days of 30. Set status to active. You will predict the notice date as 10 days before today, days left as 20, and the window as due, because 20 is within 30. On row 3 use an end date 100 days out and notice days of 30. That window should be later. Use dates you can count on a calendar before you trust the formulas.
If you do not know the notice lead time, leave Notice days blank. The notice date and the window stay blank. Do not invent a number to make the cell look finished.
Write the notice date
Notice date is blank when the end date or the notice days are blank. Otherwise it is the end date minus the notice days. With End in D2 and Notice days in E2, use =IF(OR(D2="",E2=""),"",D2-E2). Format the column as a date. If it shows a large plain number, the format is General: change it to a date. Excel stores dates as day counts, so subtracting 30 from a date gives the date 30 days earlier. That is the notice date for the number you typed. It is not a legal opinion.
=IF(OR(D2="",E2=""),"",D2-E2)A notice lead time of 0 means the notice date equals the end date. Type 0 only if that is what the lease says.
Write days left and the window
Days left are blank when status is ended or renewed, or when the end date is blank. Otherwise they are the end date minus today. With Status in G2, use =IF(OR(G2="ended",G2="renewed",D2=""),"",D2-TODAY()). Format as a number, not a date. A negative number means the end date is already past and the row is still active or in notice. The window uses the same test against the notice days: =IF(OR(G2="ended",G2="renewed",D2="",E2=""),"",IF(D2-TODAY()<=E2,"due","later")). Due means the remaining days are less than or equal to the notice days on that row. Later means the end is still further out than the notice lead time.
=IF(OR(G2="ended",G2="renewed",D2="",E2=""),"",IF(D2-TODAY()<=E2,"due","later"))TODAY moves each morning. A row that was later can become due without anyone editing the end date.
Count the rows that are due
On a sheet named Morning, the due rows are =COUNTIFS(Leases!I:I,"due"). Due rows in the building named in A2 are =COUNTIFS(Leases!B:B,A2,Leases!I:I,"due"). Ended and renewed rows are out because their window is blank. A blank window is not due. Start at row 2 if you want the header excluded. The header says Window, not due. Write the rule next to the count. Sort the lease sheet by End, oldest first, when you want the nearest end dates at the top. The count does not require that sort.
=COUNTIFS(Leases!I:I,"due")Do not count visible rows by hand after a filter and type over the formula.
Read a past end date as a signal, not as a deletion
If Days left is negative and status is still active, the end date has passed and the row was not marked ended or renewed. That is a row to open, not a row to delete. Either the end date is wrong, or the status was not updated. Fix the cell that is wrong. A negative days-left on a row marked ended should not happen, because ended blanks the days-left cell. If you see a number on an ended row, the formula was overwritten.
Filter Window to due for the call list. Filter Status to ended when you need history.
Check three rows before you share the count
End date 20 days from today and notice days 30, status active: days left 20, window due, notice date 10 days before today. End date 100 days out and notice days 30, status active: window later, because 100 is not within 30. The same 20-day end with status ended: days left blank, window blank, and the due count ignores the row. If a check fails, fix the date format or the status word before you send the count. The count is only this sheet. It does not say whether a notice was legally served.
Write the three checks on Morning if more than one person updates leases.
Template Features
Notice date from the days you typed
End date minus notice days. Blank if either input is blank.
=IF(OR(D2="",E2=""),"",D2-E2)Days left while the lease is live
End date minus today. Blank when status is ended or renewed.
=IF(OR(G2="ended",G2="renewed",D2=""),"",D2-TODAY())Window against that row's notice
Due when days remaining are within the notice days. Later otherwise. Blank when closed.
=IF(OR(G2="ended",G2="renewed",D2="",E2=""),"",IF(D2-TODAY()<=E2,"due","later"))Due count by building
COUNTIFS reads the building and the window word.
=COUNTIFS(Leases!B:B,A2,Leases!I:I,"due")History stays on the sheet
Ended and renewed rows remain. They leave the due count because the window is blank.
Concrete Examples
Inside the notice window
You want to know if a lease ending in 20 days is already inside a 30-day notice you typed.
End: 20 days after today. Notice days: 30. Status: active.
Result: Notice date is 10 days before today. Days left are 20. Window is due. The due count includes the row. The 30 is the number you typed, not a default.
Still later than the notice
Another unit ends in 100 days. Notice days on that lease are 30. You do not want it on this morning's list.
End: 100 days after today. Notice days: 30. Status: active.
Result: Days left are 100. Window is later, because 100 is greater than 30. The due count ignores the row. Change notice days to 120 and the same end date becomes due, because 100 is within 120.
An ended lease leaves the list
The term finished. You want it out of the due count and still visible.
Status changed from active to ended. End date unchanged.
Result: Days left become blank. Window becomes blank. The due count drops the row. The end date is still on the sheet.
Pro Tips
One notice number per lease
Do not put a single notice period in a corner cell and point every row at it, unless every lease truly shares it. A column you type per row survives the lease that is different. If they do share one number, you can point E2 at that cell, and you should label the cell so a later edit is obvious.
Rent is not the window
A rent figure can live in Note or in its own column that no formula reads. The morning question is the end date. Mixing a rent roll into the window formula makes the due count depend on money instead of dates.
Text dates do not subtract
If End was pasted as text, D2-E2 will not be a date and D2-TODAY() will not be a day count. Re-enter the date or convert it. Then read the window again.
Distinct from the sales list
Mandates, viewings, and commissions belong on the agent list if you also sell. This calendar starts when a lease exists and you have typed its end. Do not paste prospects into Unit.