Provide ChatGPT with your close tasks, owners, deadline offsets, dependencies, holiday calendar, and status rules, then use it to draft an auditable Excel close tracker. The workflow keeps deadlines formula-driven, completion metrics traceable, and missing ownership/timing inputs visible instead of inventing them.
ROLE
You are helping me design an auditable month-end close tracker in Excel.
CLOSE SETTINGS
- Close period: [e.g. July 2026]
- Close anchor date: [e.g. 31/07/2026]
- Standard weekend: [Saturday/Sunday / other]
- Holiday dates to exclude: [paste dates or "none"]
- Status list: Not Started | In Progress | Pending | Complete
CLOSE TASK INPUT
Provide one row per task:
Task ID | Task | Owner | Business-Day Offset | Dependency Task IDs | Notes
[Paste rows]
Rules:
1. Do not invent an Owner, offset, dependency or task.
If any required field is missing, return "INPUT NEEDED" for that field.
2. Create an Excel Table named CloseTasks with columns:
Task ID | Task | Owner | Business-Day Offset | Deadline |
Dependency Task IDs | Status | Blocker / Notes
3. Task ID must be unique.
4. Status must use Data Validation with exactly:
Not Started, In Progress, Pending, Complete.
5. Deadline:
- If weekend is Saturday/Sunday, use WORKDAY with the supplied holiday range:
=WORKDAY(CloseAnchorDate,[@[Business-Day Offset]],HolidayRange)
- If I use a non-standard weekend, use WORKDAY.INTL and state the weekend argument.
- Do not invent holiday dates.
6. Do not infer task sequence from task names. Use only the Dependency Task IDs I supply.
7. Add validation checks:
- blank Owner
- blank Business-Day Offset
- duplicate Task ID
- invalid Status
- dependency ID not found in Task ID list
8. Add summary formulas using structured references:
Total Tasks = COUNTIF(CloseTasks[Task],"<>")
Complete = COUNTIF(CloseTasks[Status],"Complete")
% Complete = IF(TotalTasks=0,0,Complete/TotalTasks)
Open = TotalTasks-Complete
9. Do not count blank placeholder rows as tasks.
10. Add an owner summary using CloseTasks structured references:
Assigned | Complete | Open | % Complete.
11. Keep "Pending" as an open status. Do not assume it means blocked unless Notes/Dependency says so.
12. Do not use VBA or macros.
13. Do not describe the tracker as final sign-off: completion status still requires reviewer evidence.
OUTPUT
1. CloseTasks table
2. Exact formulas
3. Data Validation setup
4. Owner summary formulas
5. Validation/exception checks
6. Short monthly rollover procedure explaining how to preserve the prior close record before resetting statuses
Use tab-separated tables suitable for Excel.
Replace the bracketed section with your task list, then paste the prompt into ChatGPT.
I have an Excel Table named CloseTasks. Relevant columns: CloseTasks[Task] CloseTasks[Owner] CloseTasks[Status] Owner list: [paste the exact owners to include, or tell me my Excel version if you want a dynamic UNIQUE-based owner list where supported] For each listed owner, calculate: - Assigned tasks - Complete tasks - Open tasks - Completion % Rules: - Assigned = COUNTIFS(Owner, owner, Task, "<>") - Complete = COUNTIFS(Owner, owner, Status, "Complete", Task, "<>") - Open = Assigned - Complete - % Complete = IF(Assigned=0,0,Complete/Assigned) - Do not treat blank task rows as assigned work. - Do not infer that a person with open tasks is "causing" the close delay. Only flag an overdue/blocking issue if deadline/dependency evidence supports it. Use structured-reference formulas and a tab-separated output.
Adjust column letters and row numbers to match your actual spreadsheet layout.
My month-end close tracker has a completion formula that looks wrong. Current formula: [paste formula] Relevant sample rows: Task | Status [paste several rows, including a row you believe should count as Complete] Expected result: [e.g. 3 complete out of 8 nonblank tasks = 37.5%] Please: 1. Recalculate the expected numerator and denominator from the sample. 2. Inspect the actual criteria/ranges before assuming the cause. 3. Check for: - different status labels - leading/trailing spaces or non-printing characters - numerator/denominator using different row ranges - blank task rows included in the denominator - formula pointing at the wrong column/table 4. If legacy status labels such as "Done" exist, do not silently remap them. Show the proposed mapping and ask me to confirm it before normalising history. 5. Give the corrected formula using CloseTasks structured references where possible. 6. Give one control formula for Total Tasks and one for Complete tasks so I can verify % Complete independently.
Paste both the formula and sample Task/Status rows. A wrong percentage can come from the criterion, the range, legacy text, or an incorrect denominator.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Status | Owner | Due |
| 2 | Bank reconciliation | Done | Finance team | 2nd business day |
| 3 | AP review | In progress | — | 3rd business day |
| 4 | Accruals | — | S. Okafor | — |
| 5 | Fixed asset depreciation | Pending | M. Chen | 4th business day |
| 6 | Trial balance review | — | Controller | 5th business day |
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Month-End | 31/07/2026 | Summary | 5 tasks | 2 Complete | 40% | |
| 2 | # | Task | Owner | Offset | Deadline | Status | Blocker / Notes |
| 3 | 1 | Bank reconciliation | J. Patel | 1 | 03/08/2026 | Complete | |
| 4 | 2 | AP review | M. Chen | 2 | 04/08/2026 | In Progress | Waiting on 2 vendor statements |
| 5 | 3 | Accruals | S. Okafor | 3 | 05/08/2026 | Pending | Blocked by AP review |
| 6 | 4 | Fixed asset depreciation | M. Chen | 3 | 05/08/2026 | Complete | |
| 7 | 5 | Trial balance review | Controller | 4 | 06/08/2026 | Not Started | Depends on tasks 2–4 |
Deadline (standard Sat/Sun weekend): =WORKDAY($B$1,D3,$J$2:$J$10) where J2:J10 contains supplied holiday dates. % Complete: =IF(COUNTIF(CloseTasks[Task],"<>")=0,0, COUNTIF(CloseTasks[Status],"Complete")/ COUNTIF(CloseTasks[Task],"<>")) 2 Complete / 5 tasks = 40% ✓ With Month-End = 31/07/2026 and no holiday in the interval: WORKDAY(+1) = 03/08/2026 WORKDAY(+4) = 06/08/2026 ✓ If a supplied holiday falls in the interval, WORKDAY moves the deadline accordingly.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Owner | Status | Deadline |
| 2 | Bank reconciliation | J. Patel | Complete | 03/08 |
| 3 | Cash rec review | J. Patel | Complete | 03/08 |
| 4 | AP review | M. Chen | In Progress | 04/08 |
| 5 | Depreciation | M. Chen | Complete | 05/08 |
| 6 | Lease entries | M. Chen | Pending | 05/08 |
| 7 | Accruals | S. Okafor | Pending | 05/08 |
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Owner | Assigned | Complete | Open | % Complete | Attention |
| 2 | J. Patel | 2 | 2 | 0 | 100% | |
| 3 | M. Chen | 3 | 1 | 2 | 33.3% | 2 open — review deadlines |
| 4 | S. Okafor | 1 | 0 | 1 | 0% | 1 open — review status |
| 5 | Total | 6 | 3 | 3 | 50% |
Assigned:
=COUNTIFS(CloseTasks[Owner],A2,CloseTasks[Task],"<>")
Complete:
=COUNTIFS(CloseTasks[Owner],A2,
CloseTasks[Status],"Complete",
CloseTasks[Task],"<>")
Open:
=Assigned-Complete
% Complete:
=IF(Assigned=0,0,Complete/Assigned)
M. Chen: 1 / 3 = 33.3% complete; 2 open ✓
| A | B | C | |
|---|---|---|---|
| 1 | Task | Status | COUNTIF result |
| 2 | Bank rec | Done | Not counted |
| 3 | AP review | Complete␠ | Not counted |
| 4 | Accruals | In Progress | Not complete |
| 5 | Depreciation | Complete | Counted |
| 6 | % Complete | 25% |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Raw Status | Clean Status | COUNTIF |
| 2 | Bank rec | Done | Done — review mapping | Not counted yet |
| 3 | AP review | Complete␠ | Complete | Counted |
| 4 | Accruals | In Progress | In Progress | Not complete |
| 5 | Depreciation | Complete | Complete | Counted |
| 6 | % Complete | 50% confirmed |
First remove accidental spaces: =TRIM(B2) "Complete " becomes "Complete". "Done" remains "Done" until you confirm that legacy "Done" really means the current status "Complete". Confirmed Complete rows in this example: AP review + Depreciation = 2 of 4 = 50% After you confirm any legacy mapping, normalise the data, then enforce Data Validation: Not Started / In Progress / Pending / Complete Final structured formula: =COUNTIF(CloseTasks[Status],"Complete")/ COUNTIF(CloseTasks[Task],"<>")
Provide explicit Task IDs and dependency IDs from your own close process. Do not assume universal sequencing from task names: the correct order depends on your systems, accounting process and review controls. ChatGPT should preserve the dependencies you supply rather than inventing them.
Use the ownership convention your organisation actually uses—an individual, role, or controlled team queue—and keep it consistent. Do not let ChatGPT replace a missing owner with a guessed person. Consistent owner values enable the COUNTIFS summary.
In Excel: select the Status column → Data → Data Validation → List → type exactly: Not Started,In Progress,Pending,Complete. These must match the COUNTIF criterion exactly — a row with a different value or trailing space will not be counted.
Use one close-anchor date plus an explicit business-day offset per task. For a standard Saturday/Sunday weekend, use WORKDAY with the supplied holiday range; use WORKDAY.INTL if your weekend pattern differs. Verify the first few dates each period.
Use a consistent owner identifier for each task. If your process assigns work to roles or shared queues rather than individuals, preserve that model instead of inventing personal ownership.
Designate a separate table matrix outside the main task list to display aggregated totals per person.
Use ChatGPT's prompt output to dynamically count assigned tasks vs. completed ones based on matching text criteria.
Review open tasks together with deadlines, dependencies and blockers. An owner's open-task count alone does not prove that person is delaying the close.
Check for leading/trailing spaces, non-printing characters, alternative legacy status labels, blank task rows, and mismatched numerator/denominator ranges.
After cleaning and confirming any legacy mappings, apply Data Validation with an appropriate error-alert setting so new entries use the controlled status list.
Paste your formula and sample rows into ChatGPT to diagnose likely range mismatches, text mismatches, or quoting errors.
COUNTIF-based numerator for "Complete" and a denominator that counts only nonblank Task rows. Structured references keep the ranges aligned as the Excel Table changes. Data Validation reduces new status drift, but legacy values should be reviewed before remapping them.