Excel plan step metrics

Visualize an Excel spreadsheet of project phase metrics: for each plan title and task title, calculate the step interval durations, in calendar days, and output them as tab-separated values (TSV), including an ascii bar chart per task.

You track plans (projects) and tasks, where each task moves through dated steps — for example "started", "built", "shipped" — and may also have a failure that gets detected and later resolved. This repository turns that raw date grid into interval metrics you can chart, sort, and compare:

• How long did each step-to-step transition take?
• How long did the whole task take, start to finish?
• How long did each failure take to resolve (detection to resolution)?

Four equivalent implementations are provided, so you can work inside Excel, outside it, or with no macro at all:

Project	Use it when
visualize-with-python/	You have a TSV export, a command line, and can install a couple of Python packages.
plan-task-step/	Same, but in Rust — no interpreter needed once built.
visualize.bas	You want to run it directly inside Microsoft Excel as a macro.
formulas.txt	You want native Excel cell formulas, with no macro or script at all.

The full specification lives in spec/index.md. Worked examples live under tests/, one subdirectory per scenario, each with an input.tsv and its expected expect.tsv — see Testing.

Contents

• Input format
• Output format
• Quick start
• Guide: Python program
• Guide: Rust program
• Guide: Excel VBA macro
• Guide: Excel cell formulas
• Tutorial: worked example
• How durations are calculated
• How the bar chart is drawn
• Troubleshooting
• Testing
• Contributing

Input format

Row 1 is the header row. Columns are matched by header name, not by position, so extra columns (and any column order) are fine.

Column	Required?	Content
plan_title (or project_title)	required	Name of the plan/project
task_title	required	Name of the task within the plan
step_0_date	optional	Date the task reached step 0
step_1_date	optional	Date the task reached step 1
step_2_date	optional	Date the task reached step 2
etc.	optional	Any number of step_N_date columns
failure_detection_date	optional	Date a failure was detected
failure_resolution_date	optional	Date the failure was resolved
notes	optional	Freeform text; ignored by the calculation

Rules:

• Any number of step columns works: step_0_date through step_9_date, step_10_date, and beyond. Steps are ordered by their number, even if the columns appear out of order in the sheet.
• Dates in TSV files use ISO format: YYYY-MM-DD. (The VBA macro reads whatever Excel recognizes as a date, including real date cells.)
• Rows where both plan_title and task_title are blank are skipped.
• Leading and trailing whitespace in cells is ignored.

Example (tests/basics/input.tsv):

plan_title	task_title	step_0_date	step_1_date	step_2_date	failure_detection_date	failure_resolution_date	notes
Plan A	Task A1	2026-01-01	2026-01-02	2026-01-03
Plan B	Task B1	2026-02-02	2026-02-04	2026-02-06
Plan C	Task C1	2026-03-03	2026-03-06
Plan D	Task D1	2026-04-04		2026-04-08
Plan E	Task E1		2026-05-05	2026-05-10
Plan F	Task F1				2026-06-06	2026-06-12
Plan G	Task G1				2026-07-07	2026-07-14
Plan X	Task X1	2027-01-01		2027-01-11
Plan Z	Task Z1

Output format

TSV with no header row. One row per input task. Columns:

Column	Content
plan_title	Same content as input
task_title	Same content as input
task_duration	Duration from the earliest date set anywhere in the row to the latest such date
step_0_1_duration	Duration from step_0_date to step_1_date
step_1_2_duration	Duration from step_1_date to step_2_date
etc.	One duration per consecutive pair of step columns
failure_duration	Duration from failure_detection_date to failure_resolution_date
task_bar_chart	Ascii bar chart visualizing task_duration — see How the bar chart is drawn

Rules:

• Durations are in calendar days.
• A pairwise duration (step_0_1_duration, failure_duration, etc.) is emitted as "" (an empty field) if either of its dates is blank.
• task_duration spans every date set anywhere in the row — any step date, failure_detection_date, or failure_resolution_date — from earliest to latest, not just the step dates. A task with no step dates but a recorded failure still gets a task_duration: the failure interval is its whole recorded lifecycle in that case. It is "" only when no date at all is set in the row.
• Trailing empty fields are trimmed from each row, so a row ends at its last non-empty field.

Example output (tests/basics/expect.tsv), produced from the input above:

Plan A	Task A1	2	1	1		▄▀
Plan B	Task B1	4	2	2		▄▄▀▀
Plan C	Task C1	3	3			▄▄▄
Plan D	Task D1	4				▄▄▄▄
Plan E	Task E1	5		5		▀▀▀▀▀
Plan F	Task F1	6			6	▄▄▄▄▄▄
Plan G	Task G1	7			7	▄▄▄▄▄▄▄
Plan X	Task X1	10				▄▄▄▄▄▄▄▄▄▄
Plan Z	Task Z1

Reading row by row: Task A1's steps took 1 and 1 calendar days, 2 in total. Task C1 has no step_2_date, so step_1_2_duration is empty, but task_duration (3) still comes from its two set step dates. Task D1 is missing its middle step, so both pairwise step durations are empty, yet task_duration is still 4 — the span from its first set date to its last. Tasks F1 and G1 have no step dates at all; their task_duration (6 and 7) comes entirely from failure_detection_date to failure_resolution_date. Task Z1 has no dates at all, so every field after the titles is empty and gets trimmed away.

Quick start

With a TSV export of your spreadsheet, using the Python program:

cd visualize-with-python
uv run visualize-with-python ../input.tsv -o ../output.tsv

Or the Rust program:

cd plan-task-step
cargo run --release -- ../input.tsv -o ../output.tsv

Or inside Excel: import visualize.bas into the VBA editor and run the macro VisualizePlanStepMetrics — see the VBA guide. For no macro at all, see the formulas guide.

Guide: Python program

visualize-with-python/ is a small uv-managed package (Python ≥3.13), depending on:

• pandas to read the input TSV.
• python-dateutil to parse each cell's date text — considerably more permissive than a strict-ISO parser (see parse_date()'s docstring in src/visualize_with_python/__init__.py for what that trades off).

The calculation itself is modeled as three classes — Plan, Task, and Step — each documented with runnable doctest examples; see that same file.

Setup (once):

cd visualize-with-python
uv sync

Synopsis:

uv run visualize-with-python [input.tsv] [-o OUTPUT] [-h]

• With a file argument, it reads that TSV file; otherwise it reads standard input.
• With -o/--output, it writes that file; otherwise it writes standard output.

Examples (run from inside visualize-with-python/):

# File in, file out:
uv run visualize-with-python ../input.tsv -o ../output.tsv

# As a pipeline filter:
uv run visualize-with-python < ../input.tsv > ../output.tsv

# Peek at the results in the terminal:
uv run visualize-with-python ../input.tsv | column -t -s $'\t'

# Sort by task_duration (3rd field), longest first:
uv run visualize-with-python ../input.tsv | sort -t $'\t' -k3 -rn

# Run its doctest examples:
uv run python -m doctest src/visualize_with_python/__init__.py

To get a TSV file from your spreadsheet:

• Excel: File > Save As > "Text (Tab delimited) (*.txt)" — rename to .tsv if you like; the extension doesn't matter to the program.
• Google Sheets: File > Download > "Tab-separated values (.tsv)".
• Apple Numbers: File > Export To > TSV.

Make sure date columns export as ISO YYYY-MM-DD (format the cells as ISO dates before exporting if needed) — python-dateutil accepts many other spellings too, but ISO is the only one that reads unambiguously. The output file is UTF-8 encoded, to hold the bar chart's block characters.

Guide: Rust program

plan-task-step/ is a Cargo package depending on:

• jiff for calendar dates and calendar-day arithmetic (civil::Date, Date::since).
• csv (configured with a tab delimiter) for TSV reading and writing.
• serde — csv's serde integration is what (de)serializes each record; see the "Why this became a Cargo project" section of src/main.rs's module doc comment for exactly where and why.

Like the Python program, the calculation is modeled as three types — Plan, Task, and Step — with doc comments and a #[cfg(test)] mod tests covering the same examples (plus one that checks the program's output against every fixture under tests/ directly).

Build and run:

cd plan-task-step
cargo build --release
./target/release/plan-task-step ../input.tsv -o ../output.tsv

# Or without a separate build step:
cargo run --release -- ../input.tsv -o ../output.tsv

# Run its tests:
cargo test

Synopsis:

plan-task-step [input.tsv] [-o OUTPUT] [-h]

Same argument handling as the Python program: a file argument or standard input; -o/--output or standard output.

Guide: Excel VBA macro

visualize.bas is a standard VBA module. Install it once per workbook:

1. Open your workbook in Microsoft Excel.
2. Open the VBA editor: Alt+F11 on Windows, Fn+Option+F11 on Mac (or Tools > Macro > Visual Basic Editor).
3. Either import the file — File > Import File…, choose visualize.bas — or create a module (Insert > Module) and paste the file's contents into it.
4. Close the editor and return to the worksheet that holds your data. The sheet you want to process must be the active sheet.
5. Run the macro: Alt+F8 (Mac: Tools > Macro > Macros…), select VisualizePlanStepMetrics, click Run.

The macro:

• Reads the active worksheet, using row 1 as the header row.
• Writes visualize-output.tsv, UTF-8 encoded, into the same folder as the workbook. If the workbook has never been saved, it prompts you for a save location instead.
• Finishes with a message box reporting the row count and the output path.

Tips:

• Re-running the macro overwrites visualize-output.tsv — rename or move the file first if you want to keep a previous run.
• If macros are disabled, enable them for this workbook: File > Options > Trust Center > Trust Center Settings > Macro Settings (Windows), or Excel > Preferences > Security & Privacy (Mac). Save the workbook as .xlsm ("Excel Macro-Enabled Workbook") to keep the macro in it.
• The macro only reads your sheet; it never modifies cells.

Guide: Excel cell formulas

formulas.txt is a plain-text reference of native Excel formulas that reproduce task_duration, each step interval and its ratio to task_duration, failure_duration, and a fixed-width task_bar_chart — all without a macro. Open the file for the full formulas, cell-by-cell commentary, and an "extending to more steps" section; in short:

1. Lay your data out as in Input format, one plan/task per row starting at row 2.
2. Paste the "Totals" formulas from the file once, anywhere out of the way of your data.
3. Paste the "Per row" formulas into row 2 of the columns shown, then fill down to your last data row.

Requires Excel 2013 or Excel for Microsoft 365 (the formulas use UNICHAR(), unavailable in Excel 2010 or earlier).

formulas.txt's task_bar_chart is a different rendering from the Python/Rust/VBA programs': it is padded to a fixed width (10 characters by default) with ─ filler, rather than sized to the actual task_duration — a compact sparkline suited to a single cell, rather than a literal one-character-per-day timeline. See the file itself for the exact algorithm.

Tutorial: worked example

Try the whole flow using the tests/basics fixture shipped in this repository.

Step 1 — look at the input. Open tests/basics/input.tsv. It has tasks in several shapes: some (A1, B1) moved through all of steps 1→2→3, some (C1, D1, E1, X1) have a step date missing, some (F1, G1) have no step dates but do have a failure that was detected and later resolved, and one (Z1) has no dates at all.

Step 2 — run it.

cd visualize-with-python && uv run visualize-with-python ../tests/basics/input.tsv
# or: cd plan-task-step && cargo run --quiet -- ../tests/basics/input.tsv

You should see:

Plan A	Task A1	2	1	1		▄▀
Plan B	Task B1	4	2	2		▄▄▀▀
Plan C	Task C1	3	3			▄▄▄
Plan D	Task D1	4				▄▄▄▄
Plan E	Task E1	5		5		▀▀▀▀▀
Plan F	Task F1	6			6	▄▄▄▄▄▄
Plan G	Task G1	7			7	▄▄▄▄▄▄▄
Plan X	Task X1	10				▄▄▄▄▄▄▄▄▄▄
Plan Z	Task Z1

Step 3 — interpret it. Take row B: Plan B Task B1 4 2 2 ▄▄▀▀. Task B1 reached step 0 on 2026-02-02, step 1 on 2026-02-04, step 2 on 2026-02-06 — both transitions took 2 calendar days, 4 in total (task_duration), and the bar renders that as two ▄s (step_0_1, starting at step 0, step_index 0) followed by two ▀s (step_1_2, starting at step 1, step_index 1). Take row E: Plan E Task E1 5 5 ▀▀▀▀▀. Task E1's only known interval is step_1_2 — step_index 1, odd — so it renders entirely in ▀, unlike row C's Plan C Task C1 3 3 ▄▄▄, whose only known interval is step_0_1 (step_index 0, even), rendered entirely in ▄: the character depends on the interval's own step_index, not on which intervals happen to be known in a given row. Take row D: Plan D Task D1 4 ▄▄▄▄. Task D1 has step 0 and step 2 dates but no step 1, so both pairwise durations are empty, yet its task_duration — first set date (2026-04-04) to last set date (2026-04-08) — is still 4, drawn as a single run of ▄ since neither step interval is individually known. Take row F: Plan F Task F1 6 6 ▄▄▄▄▄▄. Task F1 has no step dates, so every step duration is empty; its task_duration (6) and bar instead come entirely from failure_detection_date (2026-06-06) to failure_resolution_date (2026-06-12).

Step 4 — check it matches the fixture.

python3 run_tests.py

Every test should report PASS, including basics.

Step 5 — try the Excel route. Open tests/basics/input.tsv in Excel (it opens as a sheet with one column per field), import visualize.bas as described in the VBA guide, and run VisualizePlanStepMetrics. The resulting visualize-output.tsv contains the same rows.

Step 6 — use your own data. Lay out your spreadsheet with the input format headers, add as many step_N_date columns as your process has steps, and run whichever implementation suits you.

How durations are calculated

• A duration is the calendar-day difference between two dates: later date minus earlier date. 2026-01-01 to 2026-01-03 is 2.
• Same-day pairs yield 0. Reversed pairs (second date before the first) yield a negative number rather than an error — handy for spotting data entry mistakes.
• Pairwise step durations (step_0_1_duration, step_1_2_duration, ...) are computed between consecutive step numbers only. If a middle step's date is blank, both durations touching it are empty; the pairwise calculation does not skip over blanks to bridge step 0 to step 2.
• task_duration is the one place blanks are skipped, and the one column that also looks past the step dates: it spans from the earliest date set anywhere in the row (any step date, or either failure date) to the latest such date. It is "" only when the row has no date at all; with exactly one date set, it is 0.
• The failure duration (failure_duration) is its own pairwise column, computed the same way as a step interval, from failure_detection_date to failure_resolution_date — but it also contributes to task_duration's span, as described above.
• A cell that isn't a recognizable date is treated the same as blank.
• Times of day, if present, are ignored: only the date part counts.

How the bar chart is drawn

task_bar_chart (in the TSV output) renders task_duration as one character per calendar day:

1. Walk the step-to-step intervals in step order. Each one that is known (both its dates are set) contributes that many characters of a run, using a character chosen by that interval's own 0-based index among the step intervals — not by which other intervals in the row happen to be known: step_0_1 (index 0, starting at step_0) always renders ▄ (U+2584 bottom half block); step_1_2 (index 1, starting at step_1) always renders ▀ (U+2580 top half block); step_2_3 (index 2, starting at step_2) renders ▄ again; and so on. A row whose only known interval is step_1_2 therefore renders entirely in ▀, even though a row where step_0_1 is also known renders step_0_1's run in ▄ first — confirmed by tests/basics: compare its Plan C row (only step_0_1 known, all ▄) against its Plan E row (only step_1_2 known, all ▀).
2. task_duration can span more days than the known intervals account for — a blank middle step breaks the running total, or the span reaches into a failure date the step intervals don't cover. Whatever is left over (task_duration minus the sum of the known intervals) is appended as one final run of ▄ (index 0's character).
3. Zero, blank, or negative durations contribute no characters. A row with no task_duration gets no bar at all.

This is unverified for a row that has both a known step interval and a leftover run in the same bar — every row in the tests/ fixtures is either fully accounted for by its known intervals (leftover exactly 0) or has no known intervals at all. The leftover-uses-▄ rule above is the one directly confirmed by every all-nil-intervals row in the fixtures (tests/basics's Plan D, F, G, and X all render pure ▄), and is implemented identically across the Python, Rust, and VBA programs; re-check it against real data of the mixed shape if it matters for your use.

formulas.txt's task_bar_chart uses the same step-index rule for choosing ▄ vs ▀, but is a different, fixed-width rendering — see Guide: Excel cell formulas.

Troubleshooting

"Header row must contain plan_title (or project_title) and task_title." Row 1 must contain those headers, spelled with underscores. Header matching is case-insensitive and ignores surrounding whitespace.

A duration I expected is empty. Both dates of the pair must be present and parseable. In TSV input, dates must be ISO YYYY-MM-DD — 01/02/2026 is not parsed (deliberately, since day/month order is ambiguous). In Excel, the cell must be something Excel itself recognizes as a date.

task_duration is set but I don't have any step dates. That's expected when failure_detection_date and failure_resolution_date are both set: task_duration spans every date in the row, steps and failure alike, not just the steps. See How durations are calculated.

A row is missing from the output. Rows with both plan_title and task_title blank are skipped. Check for a stray blank row in the middle of your data.

My step columns aren't being picked up. They must match the pattern step_<number>_date exactly, e.g. step_0_date, step_12_date. Names such as step1_date or step_one_date are ignored.

Rows have different numbers of fields. That's by design: trailing empty fields are trimmed, so rows end at their last non-empty field. If your downstream tool needs rectangular data, pad the short rows or keep the trailing tabs by removing the trim step in the code.

The VBA macro processed the wrong sheet. It reads the active sheet. Click the tab of the data sheet before running the macro.

The bar chart shows garbage characters. The output file is UTF-8 (the Python program, the Rust program, and visualize.bas all write it that way). Open it with a UTF-8 capable viewer, or when importing into Excel choose "Unicode (UTF-8)" as the file origin. For formulas.txt, make sure the ─ character in the pasted formula survived the copy — retype it as UNICHAR(9472) if it turned into a ? or box.

A bar chart character looks "wrong" for a step interval I expected the other way around. The character is per-interval, not per-row: it's always ▄ for an even step_index (step_0_1, step_2_3, ...) and ▀ for an odd one (step_1_2, step_3_4, ...), regardless of what else is known in that row. See How the bar chart is drawn.

Testing

Tests live under tests/, one subdirectory per scenario, each holding an input.tsv and an expect.tsv (the expected output). Three ways to run them, all checking the same fixtures:

# The Python program's calculation, via a small standalone runner:
python3 run_tests.py

# The Python program's own doctest examples:
cd visualize-with-python && uv run python -m doctest src/visualize_with_python/__init__.py

# The Rust program's tests, including one that checks its output against
# every fixture directly:
cd plan-task-step && cargo test

run_tests.py (at the repo root) imports visualize_with_python directly from visualize-with-python/src/ — no install step needed, but pandas and python-dateutil must be importable by whichever python3 runs it (they are if you cd visualize-with-python && uv sync first, or already are on your system Python). For each subdirectory, it reads input.tsv, runs the calculation, saves the result as actual.tsv next to the fixture, and compares it against expect.tsv, reporting PASS or FAIL per test and a summary line at the end. A non-zero exit code means at least one test failed.

visualize.bas and formulas.txt aren't wired into any of the above (VBA and Excel formulas can't run from the command line); to check them by hand, open a fixture's input.tsv in Excel and compare the result against its expect.tsv the same way.

Contributing

spec/index.md is the single source of truth for the calculation: every implementation above must agree with it and with each other, and tests/ is the evidence that they do. If you're using an AI coding agent (or are one), start with AGENTS.md — it, and the deeper notes under AGENTS/, cover the project layout, the verification loop, and several design decisions that look like bugs on first read but aren't (step numbering, task_duration's exact span, the bar chart's character rule). Claude Code additionally reads CLAUDE.md.