Stop day and month mix-ups before importing business dates

A date can look valid and still mean the wrong day

Before importing dates, confirm the format used by the source and check the interpreted result against known records. In a hypothetical service schedule, 04/05/2026 could mean April 5 or May 4. Both are valid dates, so a basic error check may pass while a customer visit lands in the wrong month. An AI assistant can flag uncertainty and prepare a question for staff. It should not choose the meaning from what seems most likely.

Keep the original value beside the converted date

Use a small review table with the job reference, original date text, source file, confirmed source format, interpreted date and reviewer status. Preserve an untouched copy of the incoming file. Ask the person supplying it what the field means as well as how it is written: a requested visit date is different from a confirmed appointment. Require four-digit years for the new import process, and send two-digit years or incomplete dates for clarification.

Set the source interpretation deliberately

Microsoft documents that Power Query can use regional settings when converting dates. In its Change Type menu, Using Locale lets you choose the destination data type and the region used to interpret the source values. Choose the setting that matches the verified source convention. Changing how a date is displayed afterwards is a separate step. Check the options available in your Excel version, and inspect any earlier automatic conversion step before trusting the result.

Explains regional interpretation and the Using Locale conversion setting. Microsoft: Power Query regional settings

Try values that expose a silent swap

Test a known April 5 record written as 04/05/2026, a day greater than 12, a blank, an impossible date such as 31/04/2026 and a value from a second supplier using another convention. Write the expected result before running the import. A date greater than 12 can expose a format mismatch, but it does not prove every other row follows the same convention. If one column mixes formats without a reliable source label, stop those rows for review instead of applying one rule to all of them.

Check the destination before releasing reminders

Export a small approved sample and read it back from the receiving tool. Show the month as a word during staff review, such as 5 April 2026, to make the intended meaning clear. Confirm the receiving tool accepts the chosen format; even an unambiguous year-month-day string needs a tested import mapping. Compare job references, dates and row counts. Keep reminder sending paused until the sample passes. A date-only field should remain separate from appointment times and their time zones.

Use automation for checks, with a named exception owner

Start with one supplier export or one recurring report. A rule can validate the documented format and route exceptions; AI can explain a flagged row in plain language without changing it. Assign someone to resolve missing context, record the correction and approve the batch. Measure review minutes and date corrections before and during the pilot. Subtract the time spent handling exceptions and maintaining the mapping. Count reclaimed staff time separately from cash savings, and include setup and ongoing software costs when deciding whether a custom workflow is worthwhile.

Discuss an implementation

This guide applies across Canada. We provide remote AI consulting for businesses in Edmonton; it does not describe a local client or a staffed office.

AI consulting in Edmonton · Explore the service · Compare setup options