ElyxAI

Audit plan tracker in Excel: which planned audit is open

Internal AuditorAudit Plan

An audit plan tracker is one row per audit you already put on the plan. You type the audit, the period word, the type, the date, the owner, and the alert days for that row. Days left are the 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 Plan. Row 1 is the header. Row 2 is one audit you can check by hand: an audit name, a period word, type fieldwork, a 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 document library and no opinion on that screen. This sheet is not an audit preparation checklist. That checklist lists papers and reconciliations to gather before a review of the books. This list is the plan: fieldwork, a report, a follow-up. It is not a quality inspection, and it is not a legal matter deadline. The type is a word you type so you can filter. The sheet does not say whether a control passed. 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 report and a fieldwork date do not need the same warning. Elyx can write the days left and the window. You still type the date, the type, and the owner. The trial lasts 7 days, with no card.

The Problem

The plan fails when it lives in a notebook. A line says fieldwork is soon. Nobody can sort by date, and nobody can see which row is already inside the warning you wanted for that audit. A sentence without a date is not a tracker. A second failure is mixing the plan with a preparation checklist. A checklist answers which papers to gather before someone reviews the books. A quality sheet answers which check passed. A legal deadline list answers which matter date is open. If you paste documents, shop-floor checks, or court dates here, the morning filter no longer means which audit on the plan is open. Keep this sheet to audits, period words, types, owners, and the dates you typed. A third failure is deleting an audit 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 a control passed.

Benefits

Each audit is one row with a date, a type, an owner, and alert days you typed.

Days left are the 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 conclusion about a control.

Step-by-Step Tutorial

1

Create the list and the header

Name the sheet Plan. In row 1 type: Audit, Period, Type, Due, Owner, Alert days, Status, Days left, Window, Note. Audit is the name you use. Period is a word you type, such as a quarter label. 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 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 audit date. An audit with a fieldwork date and a report date is two rows. Do not pack both dates into one cell.

2

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 date no longer applies. Only open stays in the due window. Select Type. The source is fieldwork, report, and followup. Add another word on the Lists sheet if you need one. People must pick a word. A synonym such as finished or visit will not match the formulas. The list is a filter. It is not a statement that a control passed.

Alert days are not a status. They are a number. Do not type 7 in the status column.

3

Enter one audit you can check by hand

On row 2 type an audit, a period word, type fieldwork, a 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 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.

4

Write days left

Days left are blank when status is done or dropped, or when the date is blank. Otherwise they are the 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 date is already past and the row is still open. That is a signal to open the row. It is not a conclusion about a control.

=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 date.

5

Write the window

The window is blank when status is done or dropped, or when the date or the alert days are blank. Otherwise it is due when the 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 report and a fieldwork date can have different alert days. Type the number on the row. Do not point every row at one hidden 7 unless you mean that.

6

Count the rows that are due

On a sheet named Morning, the due rows are =COUNTIFS(Plan!I:I,"due"). Due rows for the owner named in A2 are =COUNTIFS(Plan!E:E,A2,Plan!I:I,"due"). Due fieldwork rows are =COUNTIFS(Plan!C:C,"fieldwork",Plan!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(Plan!I:I,"due")

Do not count visible rows by hand after a filter and type over the formula.

7

Read a past date as a signal, not as a deletion

If Days left is negative and status is still open, the 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 a control failed. 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 report when you only want those rows. Filter Status to done when you need history.

8

Check three rows before you share the count

Date 5 days from today and alert days 7, status open: days left 5, window 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 an opinion on a control, and it is not a preparation checklist.

Write the three checks on Morning if more than one person updates the plan.

Template Features

Days left while the audit is open

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(Plan!E:E,A2,Plan!I:I,"due")

Due count by type

The type is a word you typed. The count does not interpret it.

=COUNTIFS(Plan!C:C,"fieldwork",Plan!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 fieldwork due in 5 days on this morning's list when the alert is 7 days.

Type fieldwork. 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 rule.

Still later than the alert

A report date is 30 days out. Alert days on that row are 7. You do not want it on this morning's list.

Type report. 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 audit leaves the list

The fieldwork date is finished. You want it out of the due count and still visible.

Status changed from open to done. Date unchanged.

Result: Days left become blank. Window becomes blank. The due count drops the row. The date is still on the sheet.

Pro Tips

One alert number per date

A report may need a longer warning than fieldwork. 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

fieldwork, report, and followup let you filter. Adding a word does not say a control passed. 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 a preparation checklist

Papers to gather before a review of the books belong on a preparation checklist. Shop-floor checks belong on the quality list. Matter dates belong on the legal list. Do not paste those rows here and expect this count to mean the same thing.

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: Audit plan tracker, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related guides

Frequently Asked Questions