AI Date Filters: Include the Whole Final Day

The October report contains an entry from midnight on 31 October but misses one recorded that evening. Both rows display the same date. An AI assistant’s formula looks plausible because it says to include dates up to and including 31 October. The missing detail is the time stored alongside the date.

For a monthly spreadsheet summary, define the interval before reviewing the formula: include the start of the month and everything before the start of the next month. That gives the final day room to contain its ordinary hours, minutes and seconds. A small example makes the difference visible.

A displayed date can include a stored time

Microsoft’s guidance on Excel dates and times explains that dates are stored as serial values and times as fractions of a day. It also explains that changing the number format to General can expose those values. A date-only display is therefore insufficient to establish that a cell has no time component.

The DATE function returns the serial number for the specified year, month and day. A boundary constructed as DATE(2026,10,31) represents the start of that date. A timestamp later that day is greater than that boundary.

This matters when the task concerns a whole calendar day rather than a single instant. The word inclusive does not enlarge the boundary: less than or equal to midnight includes that midnight, while leaving later times outside it.

Five rows make the omission visible

Imagine a fictional fulfilment log in which column A contains genuine Excel date-time values and column B contains numeric quantities. Rows 2 to 6 have the following entries. All timestamps use one agreed reporting clock; no time-zone conversion is involved in this example.

• Row 2: 30 September 2026, 23:59 — quantity 5.
• Row 3: 1 October 2026, 00:00 — quantity 10.
• Row 4: 31 October 2026, 00:00 — quantity 20.
• Row 5: 31 October 2026, 18:30 — quantity 30.
• Row 6: 1 November 2026, 00:00 — quantity 40.

The intended October total is 60: quantities 10, 20 and 30. The September entry is before the period, and the November entry begins the next period. These are invented records, not measurements from a business.

A rule requiring timestamps from 1 October up to and including the start of 31 October instead selects only quantities 10 and 20. Its total is 30. The evening record is missing because 18:30 on 31 October occurs after midnight at the beginning of that day.

The row selection and totals were checked independently with Python date-time comparisons. The Excel formula below follows the documented functions, but it was not executed in Excel for this article. Test it in a copy of the intended workbook before relying on it.

Use the next period’s start as the upper boundary

Microsoft’s SUMIFS documentation describes adding values that meet multiple criteria. It places the sum range first, followed by pairs of criteria ranges and criteria. For the stated layout, the proposed formula is:

=SUMIFS(B2:B6,A2:A6,">="&DATE(2026,10,1),A2:A6,"<"&DATE(2026,11,1))

The first criterion includes timestamps at or after the start of 1 October. The second includes only timestamps before the start of 1 November. Together they describe the intended interval. In this example, the entry at exactly midnight on 1 November belongs to November.

Compare that upper criterion with "<="&DATE(2026,10,31). The difference is the boundary itself as well as the comparison operator. Merely changing less than to less than or equal to does not include the whole final day when that boundary remains its opening midnight.

Using the next period’s start also avoids inventing a final timestamp such as 23:59:59. The review can concentrate on two clearly named boundaries rather than guessing how close to midnight the stored values might be.

Confirm the inputs before changing the rule

The proposed formula assumes that column A contains numeric Excel date-time values. If an imported timestamp is text, that is a separate input problem. Inspect representative cells and confirm how the import was interpreted before treating a changed comparison as the complete repair.

For the fictional log, temporarily show both the date and time and review the boundary rows. Keep the source data unchanged while investigating. Removing every time component would change the data being examined; it is unnecessary for the stated interval rule.

Workbook settings are another separate check. MaryChuks.com’s guide to Excel’s date systems covers the interpretation of serial values between workbooks. Here, the example assumes the dates and the DATE boundaries are already interpreted consistently within one workbook.

Also state what the reporting clock means. If a real export contains timestamps from different time zones, agree how they should be interpreted before applying calendar boundaries. This formula supplies no time-zone conversion and should not be described as doing so.

Ask the assistant to show the included rows

A useful request to an assistant would specify the timestamp and quantity columns, the reporting period and the intended treatment of the exact boundaries. Ask for the expected included rows alongside the formula. This is a proposed review workflow, not a test of a particular AI product.

• Include an entry exactly at the period’s start.
• Include one at midnight on the final day and another later that day.
• Exclude an entry exactly at the next period’s start.
• Check the selected rows as well as their total.
• Confirm that the sum and criteria ranges refer to the intended matching rows.

Microsoft specifies that SUMIFS range arguments must have matching dimensions. For this example, B2:B6 and A2:A6 each cover the same five rows. Keep that correspondence visible when extending the formula to a larger dataset.

If the result differs from the expected 60 in the test workbook, investigate the stored values, ranges and criteria before accepting the summary. Record what was actually checked rather than reporting success because the formula was generated without an error message.

Make the period readable before making the total polished

The final report can state its rule plainly: timestamps from 1 October 2026 at 00:00, inclusive, to 1 November 2026 at 00:00, exclusive. That sentence gives a reviewer the same boundaries as the calculation.

For AI-assisted spreadsheet work, a convincing total should follow an explicit selection rule. Define the period, inspect its edge cases and verify the rows that contribute. The last day then receives the same attention as the first.

Sources

Primary documentation checked on 31 October 2026. The log is fictional. Python checks confirmed the example’s interval membership and arithmetic; no Excel application or AI assistant was tested. These maintained support pages have no individual publication date asserted here.

• Microsoft: Change the date system, format, or two-digit year interpretation.
• Microsoft: DATE function.
• Microsoft: SUMIFS function.