Drawing issue log in Excel: which reply date is open
A drawing issue log is one row per drawing you already issued. You type the drawing, the project, the revision, the reply date, the recipient, and the alert days for that row. Days left are the reply 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 Drawings. Row 1 is the header. Row 2 is one issue you can check by hand: a drawing name, a project, a revision word, a reply date 5 days after today, a recipient, and alert days of 7. Days left are 5. The window is due, because 5 is within 7. There is no code check on that screen. This sheet is not the daily progress a site manager keeps. That list compares a quantity done with a quantity you typed as planned. This list follows drawings you sent. It is not an inspection checklist of pass or fail, and it is not a project task schedule. Revision and recipient are words you type so you can filter. The sheet does not say whether a drawing meets a rule. You type the date. The question is which open row is inside the warning you typed. Returned and dropped rows drop out of the window. They stay on the sheet. Alert days are a number you type, because two recipients do not need the same warning. Elyx can write the days left and the window. You still type the reply date, the revision, and the recipient. The trial lasts 7 days, with no card.
The Problem
The issue log fails when it lives in email. A message says a reply is due soon. Nobody can sort by reply date, and nobody can see which drawing is already inside the warning you wanted. A sentence without a date is not a log. A second failure is mixing drawings with site quantities. A site manager's progress list answers which task is behind the quantity you typed. A project schedule answers which task date is next. An inspection checklist answers pass or fail. If you paste quantities, task bars, or inspection ticks here, the morning filter no longer means which issued drawing is waiting. Keep this sheet to drawings, projects, revisions, recipients, and the dates you typed. A third failure is deleting a drawing when the reply arrives. Set the status to returned or dropped and leave the row. The window goes blank. Deleting the row removes the only record of the revision you already sent. The sheet still does not say whether the drawing complies.
Benefits
Each issue is one row with a reply date, a revision, a recipient, and alert days you typed.
Days left are the reply date minus today while the row is open.
Due means days left are within the alert days on that row.
Returned and dropped rows stay on the sheet and drop out of the due count.
The revision is a label. It is not a code check.
Step-by-Step Tutorial
Create the list and the header
Name the sheet Drawings. In row 1 type: Drawing, Project, Revision, Reply, Recipient, Alert days, Status, Days left, Window, Note. Drawing is the name or number you use. Project is the job name. Revision is a word you type. Reply is a real date: the date you expect an answer. Recipient is the person who has the drawing. Alert days is a whole number you typed. 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 issue. The same drawing sent again at a new revision is a new row. Do not overwrite the old revision.
Lock the status words
Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is open, returned, and dropped. Open means you are still waiting. Returned means the reply you were waiting for is in. Dropped means the issue no longer applies. Only open stays in the due window. Revision stays free text, because each job uses its own marks. People must pick a status word. A synonym such as received or closed will not match the formulas.
Alert days are not a status. They are a number. Do not type 7 in the status column.
Enter one issue you can check by hand
On row 2 type a drawing, a project, a revision word, a reply date 5 days after today, a recipient, 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 reply 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 returned. 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 the reply date yet, leave Reply blank. The window stays blank. Do not invent a date to make the cell look finished.
Write days left
Days left are blank when status is returned or dropped, or when the reply date is blank. Otherwise they are the reply date minus today. With Reply in D2 and Status in G2, use =IF(OR(G2="returned",G2="dropped",D2=""),"",D2-TODAY()). Format as a number, not a date. A negative number means the reply date is already past and the row is still open. That is a signal to open the row. It is not a code check.
=IF(OR(G2="returned",G2="dropped",D2=""),"",D2-TODAY())TODAY moves each morning. A row that was later can become due without anyone editing the reply date.
Write the window
The window is blank when status is returned or dropped, or when the reply date or the alert days are blank. Otherwise it is due when the reply date minus today is less than or equal to the alert days, and later when the date is further out. Use =IF(OR(G2="returned",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 revision and the recipient are not in this formula.
=IF(OR(G2="returned",G2="dropped",D2="",F2=""),"",IF(D2-TODAY()<=F2,"due","later"))Two recipients 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(Drawings!I:I,"due"). Due rows for the project named in A2 are =COUNTIFS(Drawings!B:B,A2,Drawings!I:I,"due"). Due rows for the recipient named in A2 are =COUNTIFS(Drawings!E:E,A2,Drawings!I:I,"due"). Returned and dropped rows are out because their window is blank. A blank window is not due. Write the rule next to the count. Sort by Reply, oldest first, when you want the nearest dates at the top. The count does not require that sort.
=COUNTIFS(Drawings!I:I,"due")Do not count visible rows by hand after a filter and type over the formula.
Read a past reply date as a signal, not as a deletion
If Days left is negative and status is still open, the reply date has passed and the row was not marked returned 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 the drawing failed a rule. It says the date you typed is in the past and the status is still open. If you see a number on a returned row, the formula was overwritten.
Filter Window to due for the morning list. Filter Project when you only want one job. Filter Status to returned when you need history.
Check three rows before you share the count
Reply date 5 days from today and alert days 7, status open: days left 5, window due. Reply 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 returned: 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 a code check, and it is not the site quantity list.
Write the three checks on Morning if more than one person issues drawings.
Template Features
Days left while the issue is open
Reply date minus today. Blank when status is returned or dropped.
=IF(OR(G2="returned",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="returned",G2="dropped",D2="",F2=""),"",IF(D2-TODAY()<=F2,"due","later"))Due count by project
COUNTIFS reads the project and the window word.
=COUNTIFS(Drawings!B:B,A2,Drawings!I:I,"due")Due count by recipient
The recipient is a word you typed. The count does not interpret it.
=COUNTIFS(Drawings!E:E,A2,Drawings!I:I,"due")History stays on the sheet
Returned and dropped rows remain. They leave the due count because the window is blank.
Concrete Examples
Inside the alert you typed
You want a reply due in 5 days on this morning's list when the alert is 7 days.
Reply: 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 rule.
Still later than the alert
A reply date is 30 days out. Alert days on that row are 7. You do not want it on this morning's list.
Reply: 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 returned drawing leaves the list
The reply came in. You want the row out of the due count and still visible.
Status changed from open to returned. Reply date unchanged.
Result: Days left become blank. Window becomes blank. The due count drops the row. The revision is still on the sheet.
Pro Tips
One alert number per issue
A consultant reply may need a longer warning than an internal review. Type the days on the row. If one recipient shares one number, point those rows at a labeled cell so a later edit is obvious.
Revision is a label
The revision word lets you see which issue you sent. Changing the word does not check a code. The window formula never reads the revision.
Text dates do not subtract
If Reply was pasted as text, D2-TODAY() will not be a day count. Re-enter the date. Then read the window again.
Distinct from site progress
Quantities done against a planned quantity belong on the site manager list. Task dates belong on the project schedule. Do not paste a quantity into Drawing and expect this count to mean the same thing.