Exception log in Excel: owner, age and status
An operations exception log is one row per blockage. You type the date it was opened, the area, what cannot move, and the name of the person who has the next action. The status is a word you choose from a list: open, waiting, or closed. The age is the number of days from the opened date to today while the status is not closed. This sheet does not store a defect code, a carrier status, or a quantity to produce. If the work is "the line is down because a part has not arrived", the row says that in plain words, names an owner, and keeps the date. The part number can sit in a note column. It is not the thing you count in the morning. COUNTIFS counts open rows by area, using the status you typed. A summary at the top is those counts, not a second copy of the list. You filter the list when you want one owner's rows. You do not delete closed rows. They are how you see what was cleared. Before you trust the age, check the opened date. A date stored as text does not subtract from today, so the age stays blank or shows an error. A blank status is not "open" and it is not "closed": COUNTIFS will ignore it if your criteria ask for the word open. Correct the cell, then read the count again. On Monday you sort the open rows by age, largest first. The oldest row is the one you ask about. You do not need a chart for that. The trial lasts 7 days, with no card. Elyx can write the age column and the count on the sheet you already have. You still name the owner.
The Problem
The blockage list fails when it lives in a chat thread. A message says something is stuck. The next morning the message is buried, the owner was never named, and nobody can say how many days it has been open. Copying the thread into a cell without a date does not fix that. The date has to be a real date, and the owner has to be one name. A second failure is a sheet that tries to be every list. If you add a shipment column, a defect column, and a production quantity, the morning filter no longer answers one question. Keep this sheet to the blockage, the owner, and the age. Other facts can be a note. They are not the columns you count. A third failure is deleting rows to make the sheet look short. The open count can be done with COUNTIFS on the status. Closed rows do not inflate it. Deleting them removes the only record of what you already closed, and it tempts people to reopen work that was finished.
Benefits
Each blockage is one row with one owner and one opened date.
Age is today minus that date while the status is not closed.
COUNTIFS counts open rows by the area you typed.
Closed rows stay on the sheet and drop out of the open count.
The morning sort is by age, so the oldest open row is at the top.
Step-by-Step Tutorial
Create the list and the header
Name the sheet Exceptions. In row 1 type the headers: Opened, Area, Blockage, Owner, Status, Closed, Age, Note. Opened and Closed are dates. Area, Owner, and Status are short words. Blockage is one sentence you can read aloud. Note is optional and is not used by any formula. Freeze row 1 so the headers stay visible when you scroll. Do not merge cells. A merged header breaks a filter and breaks a table.
If you turn the range into an Excel table, formulas that use column names are easier to read. The logic below also works on a plain range.
Lock the status words
Select the Status column from row 2 downward. Use Data, Data Validation, List. The source is a small range on a sheet named Lists, with the three words open, waiting, and closed. People must pick a word. A typed synonym such as done or OK will not match the COUNTIFS criteria, and the row will vanish from the open count without being closed. Waiting means someone else owes you an answer. It is still open for the age. Only closed stops the age.
Put the list on its own sheet so nobody overwrites it while editing a blockage.
Enter one real blockage
On row 2, type a date in Opened, an area such as inbound or floor, one sentence in Blockage, and one person's name in Owner. Set Status to open. Leave Closed empty. If you do not yet have a real blockage, use a row you can check by hand: opened on a date you know, so you can predict the age before you write the formula. Do not paste a whole chat log into Blockage. One sentence is enough to find the row later.
Owner should be a name your team already uses. Two spellings of the same person split the filter.
Write the age
In the Age column, the age is blank when Status is closed. Otherwise it is today minus the Opened date. With Opened in A2 and Status in E2, the formula is =IF(E2="closed","",TODAY()-A2). TODAY recalculates when the workbook opens, so the age moves forward each morning without anyone editing it. Format the column as a number with no decimals. If the result is a date serial that looks huge, the cell format is a date: change it to Number. If the result is an error, A2 is not a date.
=IF(E2="closed","",TODAY()-A2)DATEDIF(A2,TODAY(),"D") is the same day count when you prefer a function name. Use one or the other, not both.
Record the closed date only when you close
Closed stays empty while the row is open or waiting. When the blockage is actually cleared, type today's date in Closed and set Status to closed. Do not type a closed date in advance. The age formula ignores the Closed column on purpose: the age stops because the status changed, and the Closed date is the record of when. If you need to see how long it took, a second formula can subtract Opened from Closed on closed rows only: =IF(E2="closed",F2-A2,""). That number is history. It is not the morning age.
=IF(E2="closed",F2-A2,"")Put this duration in a separate column if you want it. Do not overwrite Age, or Monday's sort loses the open rows' ages.
Count what is still open
Above the list, or on a small sheet named Morning, count open and waiting rows by area. For the area named in H2, the formula is =COUNTIFS(B:B,H2,E:E,"<>closed"). That counts every row in the area whose status is not the word closed, which includes open and waiting. A blank status is also not the word closed, so it would be counted. That is why the validation in step 2 matters. If you would rather count only the word open, use =COUNTIFS(B:B,H2,E:E,"open") and accept that waiting rows are excluded. Pick one rule and write it next to the count so the morning meeting does not debate the formula.
=COUNTIFS(B:B,H2,E:E,"<>closed")Do not COUNTIFS the whole column if row 1 contains the word you are counting. Headers are text, so they usually do not match an area name. Still, start the range at row 2 if you want to be sure.
Sort and filter for the meeting
Turn on the filter. Filter Status to show open and waiting. Sort Age from largest to smallest. The first visible row is the oldest blockage. Filter Owner when one person is in the room. Remove the filter before you leave the file, or the next person will think rows disappeared. Do not sort a sheet that has formulas referring to whole columns in a way that you then cut rows. Sorting values inside a table is safe. Cutting a row to another sheet breaks the habit of keeping history in one place.
Conditional formatting on Age, for example greater than 5, makes the old rows visible even before you sort.
Check three rows before you share the count
Pick three rows yourself. For an open row, compute today minus the opened date on paper or in a spare cell. It must match Age. For a closed row, Age must be blank. For a waiting row, Age must still be a number, because waiting is not closed. If one of the three fails, do not send the COUNTIFS total. Fix the status word or the date, then read the count again. The total is only the total of this sheet. It is not a promise about how fast the operation should run.
Write the three checks in the Note of a cell comment on the Morning sheet if more than one person updates the list.
Template Features
One owner per row
The Owner column is a single name. The morning filter uses it. Two names in one cell cannot be filtered cleanly.
Age that stops when closed
The age shows a day count only while status is not closed. Closing the row blanks the age without deleting the history.
=IF(E2="closed","",TODAY()-A2)Open count by area
COUNTIFS reads the area and the status. The number on the Morning sheet is that count, not a number you type.
=COUNTIFS(B:B,H2,E:E,"<>closed")Status list
Data validation keeps the words open, waiting, and closed. The count depends on those exact words.
Closed duration, separate from age
If you want how long a closed row took, subtract opened from closed in another column. Leave Age for the rows that are still live.
=IF(E2="closed",F2-A2,"")Concrete Examples
One open blockage you can age by hand
You open the file on a Monday. One row was opened the previous Monday. Status is open. You want the age before you trust the column.
Opened: the date seven days before today. Status: open. Area: floor. Owner: one name.
Result: Age is 7. COUNTIFS for that area, status not closed, includes this row. If Age shows a five-digit number, the cell is formatted as a date. Change it to a number. The 7 is a day count, not a target.
A waiting row still counts as open
The owner is waiting on a supplier answer. You set status to waiting and you do not want the row to disappear from Monday's list.
Opened: 3 days before today. Status: waiting. Closed: empty.
Result: Age is 3. The count that uses <>closed includes the row. The count that uses the word open does not. Use the rule you wrote next to the Morning count. Do not change the formula in the meeting.
Closing a row without deleting it
The blockage is cleared this afternoon. You want it out of the open count and still visible if someone asks what happened last week.
Status changed from open to closed. Closed date typed as today. Opened was 4 days earlier.
Result: Age becomes blank. A duration column shows 4. COUNTIFS for not closed no longer includes the row. The row is still on the sheet. Filter Status to closed if you want to read it.
Pro Tips
Keep area names on the Lists sheet
The same validation you used for status works for area. If one person types Floor and another types floor, COUNTIFS treats them as two areas unless you use a consistent list. Exact match is case-insensitive in COUNTIFS, but extra spaces are not.
Do not point Age at a clock time
If Opened includes a time, TODAY minus that value can show a decimal. INT(TODAY()-A2) keeps whole days. Agree whether the day it was opened counts as zero or one, and use the same formula on every row.
=IF(E2="closed","",INT(TODAY()-A2))One note, not a second status
The Note column can say who you called. It must not replace Status. People will filter Note badly. The words that drive the count stay in Status.
Copy the sheet at month end if you must archive
If the list gets long, copy the sheet and name the copy with the month. Leave the formulas as they are. Do not start a new file with new headers. The Morning counts read one sheet. Point them at the current sheet only.