Normalize Dates in a CSV
Date normalization puts a column holding 15/01/2024, Feb 19 2024 and 21.03.2024 into one format. The ambiguous ones are resolved by reading the whole column rather than one value, and anything unreadable is counted and quoted back rather than passed through unchanged. Everything is UTC, and it all runs in this browser tab.
Working out an age or a duration instead? DATEDIFF lives in the calculated column.
03/04/2024 is two different days
It is the fourth of March in Chicago and the third of April in Manchester, and nothing inside that cell can tell you which. Every date tool has to answer this question and most of them answer it by accident, by handing the string to whatever the platform's date parser does and getting the American reading because that is what most parsers assume.
The right answer is not in the value. It is in the column. If any value anywhere in that column has a first number above 12, the column is day-first and every other value in it can be read the same way. One 15/01/2024 settles a thousand ambiguous rows. That is the rule this page uses, and it is applied per column rather than per file, because an export can easily carry an American created_at next to a European invoice_date.
Two cases the rule does not cover, and both of them are said out loud rather than resolved quietly.
No evidence at all. A column where every day happens to be twelve or under has nothing in it to go on. The conversion still runs, and the warning says so: this column has no value with a day above 12 in it, so nothing in the data says whether 03/04 is March or April. Set Ambiguous dates and it stops guessing.
Evidence both ways. A column holding both 15/01/2024 and 01/15/2024 is not one format. It is two formats that have been concatenated, probably by an append that nobody checked. There is no reading of that column that is right for all of it, so the page refuses to pretend: it names the column and tells you some of its dates are now wrong whichever way it was read. That is a data problem, and finding it is worth more than a clean-looking output.
Worked example: four formats and a lie
Here is a column of the kind that turns up after two systems have both written to the same export:
ordered_on
15/01/2024
2024-02-02
Feb 19 2024
21.03.2024
2024-03-04T09:15:00Z
20240304
not a date
2024-13-45Tick the column, leave the output on ISO 8601, and:
ordered_on
2024-01-15
2024-02-02
2024-02-19
2024-03-21
2024-03-04
2024-03-04
not a date
2024-13-45
8 rows · ISO 8601 (YYYY-MM-DD) output · 6 dates converted · 2 values unreadable
ordered_on: 6 converted, 2 unreadable, 0 blank (day first)
Six formats read correctly: day-first slashes, ISO, a named month, dotted European, ISO with a time and a zone, and the compact eight-digit form. The day-first reading came from 15/01/2024, whose 15 can only be a day.
The last two rows are the point of the example. not a date is obvious. 2024-13-45 is the dangerous one: it is shaped exactly like an ISO date and there is no month 13 and no 45th day, so a parser that trusts the shape produces a real date somewhere in the following year. Here both are rejected, counted, and quoted back in the warning. Set Values it cannot read to Mark them #UNPARSED and they become greppable, which is what you want before a database import.
Everything is UTC, on purpose
The most familiar complaint about browser date tools is that all the dates shifted by one day. It has one cause, and it is worth understanding because it will bite you in your own scripts too.
new Date("2024-03-01").getMonth() // 1 (February) in Los Angeles
new Date("2024-03-01").getDate() // 29 in Los AngelesA bare ISO date is parsed as midnight UTC, and a local getter then reads it in your timezone, which is the previous evening anywhere west of Greenwich. Every date in the file moves back a day, and only for users in some timezones, which is why the bug survives testing.
Nothing on this page ever calls a local getter. Dates are built with Date.UTC and read with the UTC accessors from end to end, so the result is the same in Auckland and in Vancouver. A source value that carries an explicit offset, such as 2024-03-04T09:15:00+05:30, is converted to UTC using that offset, because that is what the offset is for.
The other thing this page never does is hand a string to new Date() and hope. That parser accepts almost anything, guesses differently between engines, and has no way to report that it was unsure. Every format below is matched explicitly, and a value that matches none of them is a failure rather than a guess.
What it reads, and what it writes
Input formats recognized:
2024-03-04 ISO, also with / or . as the separator
2024-03-04T09:15:00Z ISO with a time, a zone offset, or neither
04/03/2024 day-first or month-first, resolved per column
4-3-24 two-digit years: 69-99 is last century, 00-68 is this one
21.03.2024 dotted European
19 Feb 2024 day, month name, year
Feb 19, 2024 month name, day, year, with or without the comma
20240304 compact eight digits
1710892800 Unix seconds, or thirteen digits for millisecondsOutput formats offered:
ISO 8601 2024-03-04
ISO with time 2024-03-04T09:15:00Z
US 03/04/2024
EU 04/03/2024
EU dotted 04.03.2024
Long March 4, 2024
Unix seconds 1709510400
Unix milliseconds 1709510400000
Custom pattern whatever you write
The custom pattern uses the token vocabulary spreadsheets and moment-style libraries share. YYYY, YY, MMMM, MMM, MM, M, DDDD and DDD for the weekday name, DD, D, HH, hh, mm, ss, SSS, A and a. Anything outside a token is copied through, and text inside single quotes is literal, which is how you write a pattern that contains the letter D. DDD, D MMM YYYY gives Mon, 4 Mar 2024.
One switch worth knowing about: Bare timestamps decides whether a plain ten or thirteen digit number is read as Unix time. Turn it off for a column of long numeric identifiers that would otherwise be converted into dates in 2024.
Detection, and when to override it
Leave the column checkboxes empty and every column is sampled: up to 200 non-blank values, and if at least 80% of them parse, the column is treated as dates. That threshold is deliberately not 100%, because a real date column almost always has a few n/a entries in it, and it is deliberately not 50%, because a column of free text with the occasional year in it should not be converted.
Detection is a convenience rather than the main path. Tick a column and it is converted whatever the sample says, with everything unreadable counted and quoted. That is the mode to use when a column is 60% dates and you want to know exactly which rows are not, because the warning lists them.
If nothing is detected at all the page says so rather than silently doing nothing: none of the columns had enough readable dates in them to convert automatically. Ticking the column you meant is the fix, and the report then tells you what is really in it.
After the conversion
- ISO sorts correctly as text. That is the practical reason to prefer it:
2024-01-15before2024-02-02in any string sort, in any tool, with no date type required. US and EU formats do not, which is why a spreadsheet sorted on a text date column produces nonsense. - A time is kept only if you ask for it. The plain ISO output writes the date part, so a value with a time on it loses the time. Pick ISO with time, or a custom pattern, when the clock matters.
- Blank cells stay blank. They are counted separately from the failures, because a missing date and an unreadable one are different problems.
- Nothing else in the file is touched. Only the date columns change, and every other cell is copied through as the string it was.
- Unix output loses the day boundary information. A date with no time becomes midnight UTC, which is correct and is also worth knowing before you compare it against timestamps generated somewhere else.
Frequently Asked Questions
How does it know whether 03/04/2024 is March or April?
By reading the whole column rather than the one value. If any value anywhere in that column has a first number above 12, such as 15/01/2024, the column is day-first and every ambiguous value in it is read the same way. The decision is made per column, so an American created_at and a European invoice_date in the same file are handled independently.
What if nothing in the column settles it?
The conversion still runs and the warning says the guess was a guess: this column has no value with a day above 12 in it, so nothing in the data says whether 03/04 is March or April. Set the Ambiguous dates control to day first or month first and it stops guessing.
What if the column contains both readings?
Then it holds two formats concatenated together, usually by an append nobody checked, and the page says so instead of picking a winner. There is no reading that is right for all of it, so some of those dates will be wrong whichever way it goes. Finding that is more useful than a clean-looking output.
Why did my dates shift by a day in other tools?
Because a bare ISO date parses as midnight UTC and a local getter then reads it in your timezone, which is the previous evening anywhere west of Greenwich. Nothing on this page calls a local getter: dates are built and read entirely in UTC, so the result is identical in Auckland and Vancouver.
What happens to a value it cannot read?
It is counted, quoted back in the warning, and left exactly as found by default. You can also have it marked #UNPARSED so it is greppable before an import, or blanked. What it never does is pass through as though it had been understood, which is how a column ends up as ISO dates except for eleven rows nobody finds until the import fails.
Does it catch an impossible date like 2024-13-45?
Yes. It is shaped like an ISO date, and a parser that trusts the shape rolls it forward into the following year. Here the month and day are range-checked against the actual length of that month in that year, so it is rejected and counted rather than converted into a real date that never happened.
Can I write my own output format?
Yes. Choose a pattern of your own and use the usual tokens: YYYY, YY, MMMM, MMM, MM, M, DDDD and DDD for the weekday, DD, D, HH, hh, mm, ss, SSS, A and a. Text outside a token is copied through, and text inside single quotes is literal. DDD, D MMM YYYY gives Mon, 4 Mar 2024.
Is the file uploaded?
No. There is no upload endpoint on this page. The file is read, parsed and rewritten by JavaScript in your own tab, and nothing is kept between visits.
Related
One column, one format
Free, no account, no upload. Drop the file, pick a format, read what could not be parsed, take the CSV.
Back to the date normalizer