AI report day audit: preserve UTC instants across date changes

Define the report clock and its two boundaries

AI means artificial intelligence. An AI report day audit must name the clock used for the reporting date. Our fictional source timestamps are in UTC, a common time reference. Their Z suffix indicates zero UTC offset. The exercise report instead uses a fixed offset of minus five hours, written -05:00. That fixed number is a supplied calculation rule, not a claim about any city or regional daylight-saving policy.

The target report date is 2026-01-10 under that fixed offset. Our inclusion contract is explicit: local 2026-01-10 at 00:00:00 is included; local 2026-01-11 at 00:00:00 is excluded. In ordinary words, include the beginning of the day but not the beginning of the next day. This is an exercise selection rule, not an asserted RFC requirement or universal analytics default.

All events are fictional point timestamps with distinct IDs. They are not meeting durations, shifts, orders or measured customer activity. Keep the original UTC timestamps unchanged. A second column will show the same instants using the stipulated offset, followed by a per-ID inclusion decision.

For general source preparation, see the business data analysis guide. This exercise has a smaller deliverable: a saved conversion-and-boundary ledger showing which of four supplied events belong to one explicitly defined report day.

Copy full timestamps, not just their date labels

The source card below has four records. Preserve the year, month, date, hour, minute, second and Z suffix together. The T separates the date from the clock time. A timestamp names an instant here; the date portion on its own does not tell us which date that instant has under every other offset.

Count four distinct IDs before converting anything. B1 and B2 are one minute apart. B3 and B4 are also one minute apart, around the next reporting boundary. No events are missing from this fixed practice card, and no additional records should be invented to complete a day.

Create separate headings for source UTC, fixed-offset display and inclusion decision. Do not overwrite the source with its converted display. If a real source has no known offset or its clock meaning is uncertain, stop and ask what the recorded time represents. Do not pretend an unqualified clock reading is UTC merely because this teaching card uses UTC.

The spreadsheet prompt examples can help specify columns and acceptance checks. No spreadsheet function or AI account is required for the manual calculation below. We are not changing a live export or report setting.

Subtract five hours and preserve date rollover

A fixed -05:00 display is five clock hours behind UTC. For B1, January 10 at 04:59 UTC minus five hours becomes January 9 at 23:59 with the -05:00 offset. For B2, January 10 at 05:00 UTC becomes January 10 at 00:00. The date changes for the first record but not the second.

Apply the boundary contract only after converting. B1 is a minute before the target day starts, so exclude it. B2 is exactly the included start. B3 is still within the target day. B4 is exactly the excluded next-day start. The accepted ID list is B2/B3 and the count is two.

B3 is especially useful as a check: its source date says January 11, but its stipulated report-clock date is January 10. That is not a contradiction. The timestamp displays describe the same instant using different clock offsets. No event has been moved to another instant or assigned a new source time.

Keep the offset visible beside every converted clock. A bare “23:59” would lose the very information needed to explain why the source and report dates differ.

Check the same instant in reverse and compare UTC boundaries

Reverse every conversion by adding five hours to the -05:00 clock display. January 9 at 23:59 becomes January 10 at 04:59 UTC; January 10 at 00:00 becomes January 10 at 05:00 UTC. The other two rows similarly return January 11 at 04:59 and 05:00 UTC. Seconds remain zero. Each reconstructed source must match its original full timestamp.

For a second control, convert the report boundaries instead of each event. January 10 at 00:00 -05:00 is January 10 at 05:00 UTC. January 11 at 00:00 -05:00 is January 11 at 05:00 UTC. Select source events at or after the first instant and strictly before the second. This again selects B2 and B3, not B1 or B4.

Write both controls in the note: same-instant round trip per ID, then boundary comparison in UTC. Agreement verifies the exercise arithmetic and inclusion rule. It does not certify a real application’s clock, prove complete collection or show that the fictional offset matches a real location on those dates.

The working-day date audit skips dates under a fictional calendar and has no clock-time conversion. Here no weekend is skipped. We assign supplied instants to a defined reporting day; it is not a calculation of how many business days a task takes.

Which summaries conflict with the source and contract?

Claim one says: “Filter the UTC date January 10 and you have this report day.” That would select B1/B2 instead of the correct B2/B3. Both sets contain two IDs, so the total count alone would fail to expose the mistake. Preserve and compare the actual included IDs, not just the number two.

Claim two says: “Replace Z with -05:00 and leave the clock unchanged to preserve the instant.” Reject that. For example, 2026-01-10 at 05:00 -05:00 corresponds to 10:00 UTC, not B2’s 05:00 UTC. Converting the display requires adjusting the clock and possibly the date; simply changing the suffix changes the represented instant.

Claim three says: “Include B4 in both neighboring report days because it is midnight.” Under our chosen contract, the next-day start is excluded from this day and can be included as the start of the following day. Including both ends would silently change the rule and could double-count a boundary record across adjacent reports. Other real systems need their own explicit rules.

RFC 3339 explains numeric offsets in relation to UTC. Microsoft’s SwitchZone examples show that changing the numeric offset adjusts the displayed clock. We use those conversion principles only. The half-open reporting boundary is our own stated exercise contract, and no Power Query function was executed for this article.

The appointment booking guide checks a customer-facing zone and invitation. The time-tracking guide defines reporting periods and record review. Neither setup is performed here: our output is a manual per-event day-assignment ledger with an exact UTC source.

What belongs in the saved review file?

On Windows, open Start, type Notepad and launch the application. Create a document headed “Report-day offset audit V1: fictional events.” Add the source-clock and fixed-offset contract, both local boundaries, all four original UTC records and their conversion ledger. Include the B2/B3 accepted list, count two, every reverse conversion and the two UTC boundary instants.

Before saving, add all three claim decisions and their timestamp evidence. Include an unresolved real-source question: “Has the source clock meaning and the actual report-day boundary been confirmed?” Press Ctrl+S, choose Documents and save as report-day-offset-audit-V1.txt. Close the editor and reopen the file from that folder. Check that B3 retains its January 11 UTC source while its report display remains January 10, and that B4 is excluded.

For an optional AI comparison, supply only this fictional card to an assistant you already use. Copy this instruction: “Preserve every original UTC timestamp. Convert to the supplied fixed -05:00 offset, adjusting the date where needed. Target January 10 includes its midnight start and excludes the next midnight. Show per-ID inclusion, accepted IDs and count. Reverse-convert to UTC to check the same instant. Do not invent named-zone, daylight-saving or platform rules. Assess the three claims.”

Compare any proposed ledger with the human answer key. If access is unavailable, write “optional AI comparison not performed.” The manual record can still be complete. No account settings, live reports, calendar invitations or customer orders are modified.

A suggested fifteen-minute allowance is three minutes for boundary and source, four for conversions, four for controls and claims, and four for saving and reopening. This is a planning allowance, not measured user performance. In my earlier writing about perfection and effectiveness, I described project delays while pursuing every detail. Applied here, that principle means finishing a traceable small checking note before automating a report; it is not a personal analytics experiment or client result.

Finish with a bounded statement: “B2 and B3 belong to the stipulated fixed-offset report day; original UTC instants are preserved. Real source-clock accuracy and platform boundaries remain unverified.” A correct-looking count is less useful than an ID-level result another reader can reconstruct.