Are bank statements missing? Check period coverage in Excel

Working sequence

1. Set the required period, account alias and currency from the assignment and supporting records.
2. Read each statement’s declared coverage boundaries; isolate unknown dates and identify reissued copies.
3. Place required calendar days in A and inventory account/currency/start/end in H:K. Keep the source revision separately and the reviewed alias/currency in F1/G1.
4. Fill the COUNTIFS formula down the calendar, then inspect zero and multiple-coverage intervals against the original documents.

Define the calendar you actually need

Make an inventory with account alias, currency, document ID, start date and end date. Preserve the original PDFs. Quarantine unknown or reversed dates instead of inferring them from transactions. For reissued copies, record the relationship and use one authoritative interval in the coverage calculation.

Set a required start/end from the assignment. An opening or closing account may require a shorter period, but retain the evidence for that boundary. Do not silently shorten the calendar because a file is missing.

Count intervals, not transactions

Put each required day in column A. Excel’s date-series instructions explain filling sequential dates. With inventory aliases in H, currencies in I, starts in J and ends in K, a teaching formula for row 2 is:

=COUNTIFS($H$2:$H$100,$F$1,$I$2:$I$100,$G$1,$J$2:$J$100,"<="&A2,$K$2:$K$100,">="&A2)

The downloadable CSV is an inventory starter, not a formula workbook: move its account, currency, start and end columns to H:K, keeping source revision separately. F1 and G1 hold the reviewed account alias and currency. COUNTIFS requires equal-sized ranges. Use real date values, not unparsed text; localized function names and separators may differ. This formula assumes inclusive statement boundaries—check the document’s actual convention first.

Read the gaps correctly

A count of 0 means no inventoried document covers that day, not that no transactions occurred. A count of 1 means single coverage; above 1 means overlapping document periods, not proven duplicate transactions.

The invented 2024 inventory covers January–June, June–October and December. Python date-set enumeration finds 30 uncovered November days and 30 overlapping June days in a 366-day year. This is a reference calculation, not an Excel or bank-format validation. Investigate both intervals and request the missing statement before declaring the year complete. Transaction deduplication is a separate task.

Observed reference controls

Synthetic review case. The values are teaching data, not a vendor export or an import file.

Optional worksheet starter.

For a bank source available only as PDF, download LocalExtract. It processes one PDF locally into date,description,amount CSV; check the extracted rows against the issued statement before using them.

Related guide: Review duplicates across overlapping bank CSV exports.

Documentation for the added task checked October 16, 2026.