Date and Number Format Consistency Audit for a Dataset
Detect mixed date formats, text-numbers and inconsistent units in a pasted dataset sample, and write a safe standardising plan.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.
Please inspect a sample of my spreadsheet data for mixed date formats, numbers stored as text, inconsistent units and other format problems that break sorting and totals. Mention functions or menu paths only with [VERIFY]. Inputs: the column headers [COLUMN_HEADERS], at least 25 sample rows copied as plain text [SAMPLE_ROWS], my regional settings for dates and decimals [REGIONAL_SETTINGS], what each column should hold [EXPECTED_CONTENT], and the app used [SPREADSHEET_APP]. Return: (1) a column-by-column table with columns Column, Expected, Formats found, Count of each format, Ambiguous entries, Fix approach; (2) a list of ambiguous dates, such as 03/04/2024, with the possible readings, and the question I must answer to resolve them, since you must not guess; (3) a check for numbers with symbols, spaces, commas used as decimals, trailing text, or minus signs in odd places; (4) unit inconsistencies, such as kg and lb or thousands in some rows only; (5) the percent of rows affected per column with the arithmetic; (6) a step-by-step standardising plan to carry out on a copy: add a helper column, convert, check totals before and after, then replace; (7) a rule sheet for future entries. Guardrails: do not convert ambiguous values without confirmation. Do not delete original values. Describe checks to prove nothing was lost. Do not invent regional rules. Add a worked example with invented rows: 05/06/2024, 2024-06-05, "5 June 2024" and 06/05/24. Show the interpretations, which are ambiguous, and the format counts.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.