Alteryx Designer Core: Two Date Formats in One Column

Two Date Formats in One Column

Two order systems were merged last quarter. The unioned order_date column now carries at least three shapes: MM/DD/YYYY from the legacy US system, DD/MM/YYYY from the UK system, and YYYY-MM-DD from a newer export. A DateTime tool pointed at MM/dd/yyyy returns null for about 12% of the 48,000 rows, and the analyst suspects some rows are being parsed successfully but wrongly. There is no explicit country column, but there is a source_system field. About 8% of rows have both day and month parts ≤ 12, so the two slash formats are indistinguishable from the value alone. Some values also arrive with a leading non-breaking space before the ISO dates.

1. How would you establish how many rows are genuinely ambiguous versus how many can be resolved from the value itself? Give the logic, not the tool names alone.

Read the worked answer

Resolve what the value can tell you. Split the string on /; call the parts A and B.

If A > 12, the first part cannot be a month, so the row must be DD/MM/YYYY.
If B > 12, the second part cannot be a month, so the row must be MM/DD/YYYY.
If both are ≤ 12, the value is ambiguous - 05/03/2024 is a legitimate date in both readings.

Build a classification flag with a Formula (or a RegEx parse into three groups), then Summarize Count grouped by flag to get the three numbers: forced-DD/MM, forced-MM/DD, ambiguous.

Then use provenance, not arithmetic. Crosstab your classification flag against source_system. If the flag is constant within each source system - all legacy rows forced MM/DD, all UK rows forced DD/MM - then the ambiguity evaporates: you assign the format by system and the 8% stops being a problem. That is the judgement the question is really testing. Deriving a date format from the digits when a trustworthy provenance field is sitting right there is guessing with extra steps.

If provenance does not separate cleanly (mixed systems, a null source_system, a third-party file with no marker), then those rows are genuinely undecidable from the data. Count them, route them to a separate output with a clear label, and get a business owner to state the rule. Do not quietly assume US format for the remainder - a wrong assumption there is invisible in every downstream total.

2. Describe the parsing strategy end to end - what happens to the non-breaking space, and how do three input formats end up as one date type?

Read the worked answer

1. Clean before parsing. Leading non-breaking spaces are not removed by a plain Trim() in every case, and they make an otherwise valid ISO string fail to parse. Strip them explicitly with a REGEX_Replace targeting the whitespace characters, then Trim. Verify rather than assume: a LENGTH before and after shows whether the character actually went away.

2. Classify, then normalise to a single ISO string. For each branch, rebuild the value as YYYY-MM-DD:

ISO branch: take as-is (after trimming).
MM/DD/YYYY branch: Right(x,4) + '-' + Left(x,2) + '-' + Substring(x,3,2).
DD/MM/YYYY branch: same with the day and month positions swapped.

Do this with a single conditional Formula chain keyed on the classification flag from the previous task, or with three Filter branches each feeding its own conversion and a Union afterwards. The single-chain version is easier to audit because all three rules sit in one expression.

3. Convert once. Now that every row is YYYY-MM-DD, one DateTime tool (or one DateTimeParse with the ISO format) converts the whole column to a Date type. This is the key structural point: three parsers would give you three date fields and three chances to disagree; normalise to text first and you have one conversion point with one format string.

4. Fail loudly. Rows that reach the conversion still null are errors, not blanks - Filter for IsNull after the tool and pipe to a Message or a labelled error output. A silent null date is how a monthly total quietly loses a day's orders.

3. Why is 'the DateTime tool returned a value, so it's correct' a dangerous assumption here, and what check would you add?

Read the worked answer

Because a mis-format parse usually succeeds. 05/03/2024 read as MM/dd/yyyy gives 5 March; the correct UK reading is 3 May. Both are valid dates, both are in the right year, and the only symptom is a bias in the numbers that nobody can see. The 12% null rate is the visible half of the problem; the wrong-but-valid rows are the half that survives every reconciliation that isn't date-specific.

Checks worth adding.

Cross-format disagreement: parse the ambiguous rows under both formats and flag any row where the two results differ. That gives you an exact count of rows at risk, which is the number to show the business owner.
Independent constraint: if the data carries a ship_date, a created_at or a contract period, test order_date <= ship_date and order_date inside the period. A wholesale day/month swap pushes about half the rows outside a sane window, so the violation rate jumps immediately.
Distribution sanity: a day-of-month histogram built from a swapped column shows a spike on 1–12 and nothing above 12 - a useful cheap red flag when you first meet a dataset.
A permanent test: after conversion, a Filter for Null or for out-of-range dates, feeding a Message tool, turns this from a one-off investigation into a tripwire on every future run.

The general principle: a type conversion validates syntax, not meaning. When you know the same column contains more than one syntax, the meaning has to be checked structurally.