DATEVALUE interprets supported date text using regional conventions. A successful conversion can still produce the wrong day if the source’s month/day order differs from the spreadsheet’s expectations.
Identify the source format
Check the original system’s date definition and several examples. Values such as 03/04 are ambiguous without that context. Inspect the spreadsheet’s locale under File > Settings.
Keep the imported string unchanged in a raw column. Do not infer a convention solely because one interpretation happens to produce a valid calendar date.
Construct known components explicitly
If the source establishes year 2026, month 4, and day 3, =DATE(2026,4,3) creates the intended date without a slash-order assumption. Use numeric components from the documented source structure.
DATE can normalize out-of-range components rather than rejecting them. For example, an oversized day can roll into another month. Validate the components separately when malformed source dates must be rejected.
Verify the parsed calendar date
Apply a clear date display and compare day, month, and year with known source records. Include a day above twelve, month boundaries, and leap-year examples relevant to the dataset.
DATEVALUE expects text; applying it to an already numeric date cell can produce #VALUE!. Inspect types before adding conversions. If an ambiguous original has already been replaced by a numeric date, a locale change may not recover the original intent. Retrieve the source or flag uncertainty instead of converting the same value repeatedly until the display looks plausible. Record the accepted input format for future imports.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.