# Test the dates on both sides of month end

Four dated witness documents test a real Power Query month boundary. Includes native Excel results, the workbook and a corrected floating-point assertion.

Published 2026-11-15T07:30:00Z by Renaissance Superintelligence Corporation (RSI).

Test the last day before the month, its first and last days, and the first day after it. Our native Excel run assigned all four witnesses correctly: October increased by $6 and year-to-date through October by $7. An exact floating-point assertion initially failed despite correct period assignments.

## Use amounts that reveal which dates were included

We added four synthetic documents to a fresh expense-workbook copy: September 30 for $1, October 1 for $2, October 31 for $4 and November 1 for $8. Each received its own identifier. The distinct amounts let the aggregate change identify the combination included, while row-level checks independently inspected every assigned period.

The starting October actual was $686,500 and year-to-date actual through October was $6,200,758. The expected changes were $6 for October and $7 for year-to-date. November’s $8 belonged to neither total. We wrote those expectations before refreshing the Actuals query.

This fixture takes US-formatted posting dates. Microsoft’s Date.FromText documentation supports explicit format and culture options. If your source uses another convention, test that convention directly; a successful US-format test does not validate day-first dates or timestamp-to-timezone conversion.

## Inspect each assigned period and both totals

After the native query refresh and full calculation, the four records mapped to September, October, October and November respectively. The clean table contained 484 documents. October was $686,506; year-to-date was approximately $6,200,765.

Checking the detail and aggregate catches different failures. A correct total can conceal offsetting row mistakes. Correct individual dates can coexist with a report that filters the wrong period. This test requires both to agree.

Witness documents and expected inclusion

Posting date | Amount | Assigned period | October / YTD through October

09/30/2026 | $1 | 2026-09 | No / Yes

10/01/2026 | $2 | 2026-10 | Yes / Yes

10/31/2026 | $4 | 2026-10 | Yes / Yes

11/01/2026 | $8 | 2026-11 | No / No

## The first failure was in our assertion

The first controller compared the year-to-date result with 6200765 using exact equality. Excel returned 6200765.0000000009, represented in the published JSON as 6200765.000000001. The period assignments and financial result were correct, but the assertion reported failure.

We replaced that comparison with an explicit absolute tolerance below half a cent and reran the experiment. It passed. Microsoft documents how binary floating-point representation can produce small arithmetic differences. This explains the observed residual; it does not authorize ignoring meaningful financial discrepancies.

Choose the comparison rule from the data contract. Here the input amounts are USD cents, and a $0.005 tolerance distinguishes a binary residual from a one-cent error. A model with fractional-cent rates or another currency needs its own precision and rounding policy. Do not round away a discrepancy before understanding it.

```powershell
if ([math]::Abs($actualYtd - 6200765) -ge 0.005) {
    throw 'YTD does not reconcile within the fixture tolerance'
}
```

## Adapt the boundary cases to the close policy

Download the [saved boundary workbook](/research/native-office-2026-11-15/period-boundaries.xlsx), [result including the prior assertion failure](/research/native-office-2026-11-15/period-boundaries.json) and [script](/research/native-office-2026-11-15/native-period-boundaries.ps1). Supply the synthetic [expense workbook](/examples/excel-to-powerpoint/October%20close.xlsx) as expense-original.xlsx in a new directory.

The script creates its own copy and adds the four witnesses. It tests calendar-month date strings, not a fiscal calendar, business-day cutoff, timezone rule or late-posting policy. Add cases for those boundaries when they determine your reporting period.

Use [Panes desktop execution](/docs#commands) to run native Excel. Keep this date test beside the [duplicate-document guard](/blog/power-query-duplicate-document-guard) and [query freshness witness](/blog/prove-excel-power-query-refreshed): correct dates do not establish unique or current inputs.

## Evidence

Prepared scripted execution on one retained 4-vCPU, 16-GB Windows research computer, November 15, 2026. Excel 16.0 build 20527. Query refresh took 1.547 seconds; the native script reached its saved result in 4.656 seconds before finally-block cleanup, API time was 5.377 seconds and controller wall time 5.505 seconds. The earlier exact-equality assertion failure is retained in the result record. One observed run, not an autonomous-agent or fleet benchmark. Per-task operating cost is unmeasured. Public Office copies have author metadata redacted; all other ZIP members are identical to the native outputs. They were reopened read-only in Office without changing their bytes.

## Sources

- [Microsoft: Date.FromText format and culture](https://learn.microsoft.com/en-us/powerquery-m/date-fromtext)

- [Microsoft: floating-point arithmetic in Excel](https://learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/excel/floating-point-arithmetic-inaccurate-result)

- [Exact native results and corrected assertion](https://usepanes.com/research/native-office-2026-11-15/period-boundaries.json)

- [Public-file identity and native reopen](https://usepanes.com/research/native-office-2026-11-15/publication-manifest.json)