Checking Units and Date Formats

Why Units and Date Formats Need Checking

A column can contain values that look orderly but do not mean the same thing. For example, a set of temperatures might mix degrees Celsius and degrees Fahrenheit. A date column might mix year-month-day strings with day-month-year strings. If such entries are treated as though they share one representation, a calculation or sort may be misleading. A value such as 68 could mean a temperature in Fahrenheit, while the same number in Celsius describes a very different temperature.

Checking units and date formats is a data-quality task: establish what each value represents, identify inconsistent representations, and make only conversions supported by the available information. A consistent column is not necessarily a correct column, so the original values and the rules used to interpret or convert them should remain available for checking.

Checking and Converting Measurement Units

Start by identifying what a column measures and the unit attached to each value. Look at column names, labels, data dictionaries, collection forms, and other source information. Do not infer a unit from a number's size alone: plausible values can occur in more than one unit, and an implausible value could signal either a unit mismatch or a separate recording error.

Choose one target unit for the column and apply a stated conversion rule to every value expressed in a different unit. A conversion changes the numerical representation, not the underlying quantity. For a valid conversion, the converted value must represent the same measurement in the target unit. Keep the raw entry, the unit it used, and the converted value distinguishable; overwriting the only copy makes it harder to find or correct a mistake.

Dates: Interpret Before Standardizing

Dates require particular care because different formats can assign different meanings to the same string. The entry 04/05/2025 could mean April 5 or 4 May. Neither interpretation is justified by the string alone. Before converting it, consult the data source's documented convention or other reliable evidence. A value such as 13/05/2025 cannot be month/day/year because there is no thirteenth month, but that does not establish the meaning of every other entry in the column.

Once each date's meaning is established, an unambiguous and widely used representation is the ISO-style calendar form YYYY-MM-DD, with a four-digit year, two-digit month, and two-digit day. For example, 4 May 2025 becomes 2025-05-04. This representation helps prevent confusion between month-first and day-first formats. It does not, by itself, settle questions about time of day, time zone, or which calendar a source used; those details matter if the data include them.

Why Conversion Rules Work

A conversion rule should preserve the quantity or date represented, not merely make the entries look alike. For a simple unit conversion, the equivalence between units supplies the factor. For example, because one inch is exactly 2.54 centimeters, \(x\) inches corresponds to \(2.54x\) centimeters. The units show why the multiplication is appropriate:

The inch units cancel, leaving centimeters. Multiplying centimeters by the same factor would not convert them to inches; it would move in the wrong direction. For offset scales, the conversion must include both the offset and scale factor. Fahrenheit and Celsius, for instance, do not have zero points that coincide, so multiplying a Fahrenheit value by \(5/9\) alone is not a valid conversion.

Date standardization follows a related logic. It changes the written order of a known year, month, and day; it cannot determine those parts when the original string is ambiguous. In both kinds of conversion, the evidence and rule must be established before changing the representation.

Record the Rules and Keep the Evidence

For each affected variable, record the target unit or date format, the conversion rule, and how the original entries were identified. For example, a rule might state: “Temperatures recorded in Fahrenheit were converted to Celsius using \(C=(F-32)\times 5/9\); entries already in Celsius were retained.” A date rule might state: “Slash-separated dates follow the source's documented day/month/year convention and were reformatted as YYYY-MM-DD.” Include the source of the convention when it is available.

Retain the raw values, or otherwise preserve an unchanged source copy, so that a reviewer can compare each original entry with its standardized value. If the unit or date convention cannot be established, flag the entry for clarification rather than silently assigning an interpretation. A blank or flagged value is more honest than a confident-looking conversion based on a guess.

Common Mistakes

• Assuming a number's unit from its size. A value that seems typical in one unit may still have been recorded in another. Check labels or source documentation before converting.
• Applying a simple multiplier to an offset scale. Multiplying a Fahrenheit reading by \(5/9\) omits the offset. Use the complete formula \(C=(F-32)\times 5/9\).
• Guessing what an ambiguous date means. Reading 04/05/2025 as either April 5 or 4 May without evidence is not standardization. Confirm the source convention or leave the meaning unresolved.
• Replacing the raw data with converted values. Keeping only the cleaned entries removes evidence needed to audit or revise the conversion. Preserve the original value and the rule.
• Rounding more than the data justify. A long decimal result does not imply precise measurement. Choose and record a sensible rounding policy that does not suggest unsupported accuracy.