How to convert hundreds of text dates to a uniform format without datevalue()
- Step 1
- Step 2Drop the workbook — Add the
.xlsxor.csvwhose date column is stored as text. - Step 3Name the column(s) or auto-detect — Type the text-date headers comma-separated, or leave blank to auto-detect across the sheet.
- Step 4Choose the output format — ISO 8601 is the safe, universal choice. Pick a regional format only if a human report needs it.
- Step 5Run — no formulas needed — The whole column is rewritten at once into
dates-standardized.xlsx. There are no helper columns to clean up afterward. - Step 6Convert to real dates only if you need math — If you need serial dates for arithmetic, the uniform output makes an Excel Text-to-Columns conversion quick and reliable. If a cell buries the date inside other text (e.g.
Invoice 12 Jan 2024 - paid), pull it out first with the Regex Extractor, then standardize the new column.
DATEVALUE() vs the Date Standardizer
DATEVALUE returns a serial number per cell and errors on many formats. The Date Standardizer rewrites whole columns to a uniform text format and reports failures instead of erroring.
| Aspect | Excel DATEVALUE() | Date Standardizer |
|---|---|---|
| Scope | One cell per formula + helper column | Whole column(s) in one pass |
| Output type | Numeric serial date | Uniform text in your chosen format |
12 January 2024 | Often #VALUE! depending on locale | Converts to 2024-01-12 |
2024/01/12 | Often #VALUE! | Converts to 2024-01-12 |
| Failure handling | #VALUE! left in the cell | Cell unchanged + listed in findings report |
| Formulas / helper columns | Required | None |
| Format control | Inherits cell number format | Six fixed output formats |
What chrono-node does with common date strings
Behaviour verified by running the bundled chrono-node parser. The parser uses its default English settings — month-first for ambiguous slash dates — and there is no locale switch in this tool.
| Input cell value | Parses? | Result (to ISO 8601) | Note |
|---|---|---|---|
2024-09-29 | Yes | 2024-09-29 | Already ISO — passes through unchanged |
09/07/2024 | Yes | 2024-09-07 | Read as month-first (US): Sep 7, not 9 July |
13/07/2024 | Yes | 2024-07-13 | Day-first used only because 13 cannot be a month |
29 September 2024 | Yes | 2024-09-29 | Natural-language text date |
29-Sep-24 | Yes | 2024-09-29 | Two-digit year is expanded |
2024-09-29 14:30 | Yes | 2024-09-29 | Time is parsed then dropped by the date-only token |
next friday | Yes | (date relative to today) | Relative phrases resolve against the run date |
Q3 2024 | No | (left unchanged) | Listed in the findings report |
45899 (number as text) | No | (left unchanged) | A bare serial typed as text does not parse |
Tier limits for the Date Standardizer
The Date Standardizer is gated to Pro and above — a Free account cannot run it. Limits below are the real Excel-family numbers. One file is processed per run.
| Tier | Max file size | Max rows | Runs this tool? |
|---|---|---|---|
| Free | 5 MB | 10,000 | No — Pro required |
| Pro | 50 MB | 100,000 | Yes |
| Pro-media | 200 MB | 500,000 | Yes |
| Developer | 500 MB | Unlimited | Yes |
Cookbook
Side-by-side with what DATEVALUE would do. The tool's value is bulk conversion and tolerance for messy formats — not in-cell date math.
Formats DATEVALUE chokes on
These commonly produce #VALUE! in DATEVALUE (especially outside US locales). The standardizer converts them without complaint.
Input (text) DATEVALUE() Date Standardizer (ISO) -------------------- ------------ ----------------------- 12 January 2024 #VALUE! 2024-01-12 12-Jan-24 #VALUE! (varies) 2024-01-12 2024/01/12 #VALUE! 2024-01-12 Jan 5 2024 #VALUE! 2024-01-05
Bulk, no helper column
The DATEVALUE workflow needs a second column and a formula dragged down every row. The tool rewrites the original column directly.
DATEVALUE workflow: B2: =DATEVALUE(A2) (then format as date, drag to B10001, delete A) Date Standardizer workflow: Drop file -> pick ISO 8601 -> run -> done (column A rewritten in place, no extra columns)
Convert only specific columns
Name the text-date columns so numeric IDs or amounts aren't touched by auto-detect.
Columns box: invoice_date, paid_date invoice_date: "12 Jan 2024" -> 2024-01-12 paid_date: "15/01/2024" -> 2024-01-15 (month-first: Jan 15) amount: 12.50 -> untouched
Failures are reported, not left as #VALUE!
Where DATEVALUE would stamp #VALUE! into the cell, the tool leaves the original and tells you exactly where the problem is.
Before (column: due_date) 12 January 2024 ask Bob 2024/02/01 After (ISO 8601) 2024-01-12 ask Bob <- unchanged (would be #VALUE! with DATEVALUE) 2024-02-01 findings.failedCells: Row 3, due_date: "ask Bob"
When you actually want serial dates
If the end goal is Excel date math, run the tool to get a uniform column, then do one Text-to-Columns conversion in Excel — far easier than DATEVALUE on mixed formats.
Step 1 (tool): mixed text dates -> uniform ISO 8601 text
Step 2 (Excel): select column -> Data > Text to Columns >
Finish with date format -> real serial dates
Result: one clean conversion instead of per-cell DATEVALUE.Edge cases and what actually happens
Output is text, not a serial date
Known behaviourUnlike DATEVALUE, the tool writes text in your chosen format, not a numeric serial. For Excel arithmetic, do one Text-to-Columns conversion on the uniform result.
Ambiguous low-number slash date
Parsed (month-first)05/06/2024 is read month-first as May 6, matching DATEVALUE's US behaviour but not UK intent. There's no locale toggle; spot-check day-first columns.
Value chrono can't parse
Failed parseWhere DATEVALUE returns #VALUE!, the tool leaves the original text and records it in failedCells — no error stamped into the cell.
Bare serial number stored as text
Failed parseA number like 45899 as text doesn't parse (chrono needs a date-shaped string). Make it a real date in Excel first.
Two-digit year
Expanded12-Jan-24 expands to 2024 — verify the century for historical data.
Date with a time
Time dropped12 Jan 2024 09:00 converts to 2024-01-12; the time is discarded by the date-only output formats.
Free-text dates like `next monday`
Resolves to todaychrono resolves relative phrases against the run date — a difference from DATEVALUE, which would just error. Audit columns that mix real dates and free text.
Auto-detect skips a sparse column
Possible missIf fewer than 60% of the first 20 non-empty sampled rows parse, the column isn't auto-detected. Name it explicitly to force conversion.
More than 50 failures
Truncated listfailedCells shows the first 50; failedCount gives the true total.
File over tier limit
RejectedFiles above 50 MB / 100,000 rows (Pro) are refused before processing.
Frequently asked questions
What does this do that DATEVALUE() can't?
It converts a whole column at once with no helper column and no formula, and it parses many formats DATEVALUE rejects — 12 January 2024, 2024/01/12, Jan 5 2024, 12-Jan-24. DATEVALUE works one cell at a time and errors on those.
Does the output use Excel serial numbers like DATEVALUE?
No. DATEVALUE returns a serial number; this tool writes a uniform text value in your chosen format (ISO 8601 by default). If you need serial dates for math, convert the uniform output once in Excel via Text-to-Columns.
Which formats does DATEVALUE fail on that this handles?
12 January 2024, 12-Jan-24, 2024/01/12, Jan 5 2024 and most non-US natural-language dates commonly produce #VALUE! in DATEVALUE. chrono-node handles all of these.
Do I need a helper column?
No. The tool rewrites the date column directly. There's nothing to copy, paste-special, or delete afterward.
Can I convert only certain columns?
Yes. Type the date column headers into the comma-separated box. Leave it blank to auto-detect date columns across the sheet.
What happens to a cell DATEVALUE would error on?
Instead of #VALUE!, the tool leaves the original value untouched and lists it in the findings report with its exact row and column.
How does it treat ambiguous dates like `05/06/2024`?
chrono reads it month-first (May 6), the same as DATEVALUE in a US locale. There's no day-first toggle, so spot-check UK day-first columns.
How many rows can it convert at once?
100,000 on Pro, 500,000 on Pro-media, unlimited on Developer — well beyond what dragging a DATEVALUE formula is practical for.
Is the file uploaded to a server?
No. Parsing and conversion happen entirely in your browser.
Does it handle existing real Excel dates in the same column?
Yes — SheetJS decodes true date cells to Date objects and reformats them alongside the text ones, so the whole column ends up uniform.
What about a date with a time component?
The date is converted and the time is dropped, because the output formats are date-only.
What if I want a different layout than ISO?
Pick one of the six formats: ISO 8601, UK, US, dd-MM-yyyy, MM-dd-yyyy, or Long. There's no custom format box.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.