Matter deadline tracker in Excel: which due date is open
A matter deadline tracker is one row per deadline on a matter you already have. You type the matter, the counterparty, the deadline type, the due date, the owner, and the alert days for that row. Days left are the due date minus today while the row is still open. A window says due when the days left are within the alert days you typed, and later when the date is still further out. The first screen is a sheet named Matters. Row 1 is the header. Row 2 is one deadline you can check by hand: a matter name, a counterparty, type filing, a due date 5 days after today, an owner, and alert days of 7. Days left are 5. The window is due, because 5 is within 7. There is no clause library and no advice on that screen. This sheet is not the contract tracker used by an office manager or an executive assistant. Those lists follow vendor contracts and admin renewals. This list follows matters. Deadline type is a word you type so you can filter filings, responses, hearings, and signatures. The sheet does not tell you which deadline the law requires. It does not draft a response. You type the date. The question is which open row is inside the warning you typed. Done and dropped rows drop out of the window. They stay on the sheet. Alert days are a number you type, because a hearing and a filing do not need the same warning. Elyx can write the days left and the window. You still type the due date, the type, and the owner. The trial lasts 7 days, with no card.
The Problem
The deadline list fails when it lives in a notebook. A line says a response is due soon. Nobody can sort by due date, and nobody can see which row is already inside the warning you wanted for that matter. A sentence without a date is not a tracker. A second failure is mixing matters with office contracts. An office manager's contract list and an executive assistant's contract list answer which vendor agreement renews. If you add office suppliers and admin renewal dates here, the morning filter no longer means which matter deadline is open. Keep this sheet to matters, counterparties, types, owners, and the dates you typed. A third failure is deleting a matter when the date passes. Set the status to done or dropped and leave the row. The window goes blank. Deleting the row removes the only record of the date you already passed. The sheet still does not say whether you met a legal duty.
Benefits
Each deadline is one row with a due date, a type, an owner, and alert days you typed.
Days left are the due date minus today while the row is open.
Due means days left are within the alert days on that row.
Done and dropped rows stay on the sheet and drop out of the due count.
The type is a filter. It is not a legal conclusion.
Step-by-Step Tutorial
Create the list and the header
Name the sheet Matters. In row 1 type: Matter, Counterparty, Type, Due, Owner, Alert days, Status, Days left, Window, Note. Matter is the file name you use. Counterparty is the other party. Type is a word from a short list. Due is a real date. Owner is the person who holds the date. Alert days is a whole number you typed: how many days before the due date you want the row on the morning list. Days left and Window are formulas. Status is a word from a list. Note is optional. Freeze row 1. Do not merge cells.
One row per deadline. A matter with a filing date and a hearing date is two rows. Do not pack both dates into one cell.
Lock the status words and the type words
Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is open, done, and dropped. Open means the date is still yours to meet. Done means the work for that date is finished. Dropped means the deadline no longer applies. Only open stays in the due window. Select Type. The source is filing, response, hearing, and signature. Add another word on the Lists sheet if you need one. People must pick a word. A synonym such as finished or court will not match the formulas. The list is a filter. It is not a statement of what a court requires.
Alert days are not a status. They are a number. Do not type 7 in the status column.
Enter one deadline you can check by hand
On row 2 type a matter, a counterparty, type filing, a due date 5 days after today, an owner, and alert days of 7. Set status to open. You will predict days left as 5 and the window as due, because 5 is within 7. On row 3 use a due date 30 days out and alert days of 7. That window should be later. On row 4 use the 5-day date and set status to done. Days left and the window should go blank. Use dates you can count on a calendar before you trust the formulas.
If you do not know how much warning you want, leave Alert days blank. The window stays blank. Do not invent a number to make the cell look finished.
Write days left
Days left are blank when status is done or dropped, or when the due date is blank. Otherwise they are the due date minus today. With Due in D2 and Status in G2, use =IF(OR(G2="done",G2="dropped",D2=""),"",D2-TODAY()). Format as a number, not a date. A negative number means the due date is already past and the row is still open. That is a signal to open the row. It is not a legal conclusion.
=IF(OR(G2="done",G2="dropped",D2=""),"",D2-TODAY())TODAY moves each morning. A row that was later can become due without anyone editing the due date.
Write the window
The window is blank when status is done or dropped, or when the due date or the alert days are blank. Otherwise it is due when the due date minus today is less than or equal to the alert days, and later when the date is further out. Use =IF(OR(G2="done",G2="dropped",D2="",F2=""),"",IF(D2-TODAY()<=F2,"due","later")). Due means the remaining days are inside the warning you typed on that row. Later means they are not. The type and the owner are not in this formula.
=IF(OR(G2="done",G2="dropped",D2="",F2=""),"",IF(D2-TODAY()<=F2,"due","later"))A hearing and a filing can have different alert days. Type the number on the row. Do not point every row at one hidden 7 unless you mean that.
Count the rows that are due
On a sheet named Morning, the due rows are =COUNTIFS(Matters!I:I,"due"). Due rows for the owner named in A2 are =COUNTIFS(Matters!E:E,A2,Matters!I:I,"due"). Due filings are =COUNTIFS(Matters!C:C,"filing",Matters!I:I,"due"). Done and dropped rows are out because their window is blank. A blank window is not due. The header says Window, not due. Write the rule next to the count. Sort by Due, oldest first, when you want the nearest dates at the top. The count does not require that sort.
=COUNTIFS(Matters!I:I,"due")Do not count visible rows by hand after a filter and type over the formula.
Read a past due date as a signal, not as a deletion
If Days left is negative and status is still open, the due date has passed and the row was not marked done or dropped. That is a row to open, not a row to delete. Either the date is wrong, or the status was not updated. Fix the cell that is wrong. The negative number does not say you breached a duty. It says the date you typed is in the past and the status is still open. A days-left number on a row marked done should not happen, because done blanks the cell. If you see a number on a done row, the formula was overwritten.
Filter Window to due for the morning list. Filter Type to hearing when you only want those rows. Filter Status to done when you need history.
Check three rows before you share the count
Due date 5 days from today and alert days 7, status open: days left 5, window due. Due date 30 days out and alert days 7, status open: window later, because 30 is not within 7. The same 5-day date with status done: 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 is not legal advice.
Write the three checks on Morning if more than one person updates matters.
Template Features
Days left while the matter is open
Due date minus today. Blank when status is done or dropped.
=IF(OR(G2="done",G2="dropped",D2=""),"",D2-TODAY())Window against that row's alert
Due when days remaining are within the alert days you typed. Later otherwise. Blank when closed.
=IF(OR(G2="done",G2="dropped",D2="",F2=""),"",IF(D2-TODAY()<=F2,"due","later"))Due count by owner
COUNTIFS reads the owner and the window word.
=COUNTIFS(Matters!E:E,A2,Matters!I:I,"due")Due count by type
The type is a word you typed. The count does not interpret it.
=COUNTIFS(Matters!C:C,"filing",Matters!I:I,"due")History stays on the sheet
Done and dropped rows remain. They leave the due count because the window is blank.
Concrete Examples
Inside the alert you typed
You want a filing due in 5 days on this morning's list when the alert is 7 days.
Type filing. Due: 5 days after today. Alert days: 7. Status: open.
Result: Days left are 5. Window is due. The due count includes the row. The 7 is the number you typed, not a court rule.
Still later than the alert
A hearing is 30 days out. Alert days on that row are 7. You do not want it on this morning's list.
Type hearing. Due: 30 days after today. Alert days: 7. Status: open.
Result: Days left are 30. Window is later, because 30 is greater than 7. Change alert days to 30 and the same date becomes due.
A done deadline leaves the list
The filing was sent. You want it out of the due count and still visible.
Status changed from open to done. Due date unchanged.
Result: Days left become blank. Window becomes blank. The due count drops the row. The due date is still on the sheet.
Pro Tips
One alert number per deadline
A hearing may need a longer warning than a signature. Type the days on the row. If a whole type shares one number, point those rows at a labeled cell so a later edit is obvious.
Type is a label
filing, response, hearing, and signature let you filter. Adding a word does not create a legal duty. The window formula never reads the type.
Text dates do not subtract
If Due was pasted as text, D2-TODAY() will not be a day count. Re-enter the date. Then read the window again.
Distinct from office contract lists
Vendor renewals and admin contracts belong on the office manager list and the executive assistant list. Do not paste an office supplier into Matter and expect this count to mean the same thing.