Convert Number and Date Formats

What this does

You export something from a system that thinks in a different locale to your spreadsheet. The amounts come out as 1.234,56 and the dates as 31.12.2026. Both look fine in the cell. Neither is a number or a date as far as the spreadsheet is concerned — they are text — so SUM returns 0, sorting puts 9 above 10, and nothing anywhere tells you why.

This converts a whole pasted range in one go, working out each column separately, and leaves alone anything it is not sure about. The usual first sign of the problem is alignment: spreadsheets right-align numbers and left-align text, so a column of right-looking values sitting hard against the left edge is a column that is not what it appears to be.

How to use it

1. Copy the range out of your sheet — all the columns, not one at a time — and paste it in.
2. Look at the column list. Each one says what it was taken to be and what happened to it; override any that are wrong.
3. Pick what you are converting to. Neutral is the right answer unless you are feeding a system that demands otherwise.
4. Copy the result and paste it back over the range it came from.

A worked example

The Try an example button loads five rows of a German ERP export, and every column in it is a different problem. The article numbers are 00123 and 00456— codes, not numbers, and converting them would drop the leading zeros and destroy them. The description column is ordinary text. The amounts are comma-decimal, and include one negative written (89,90) in accounting brackets and another written 2.500,00- with the minus trailing, which is how SAP writes it.

One amount is 1 999,00, grouped with a no-break space — invisible, not a normal space, and the single most common reason a French or Swiss export refuses to parse anywhere. The dates are day-first and dotted. Press the button and every one of those is handled, while the article numbers and descriptions come back byte for byte as they went in.

Assumptions and edge cases

Anything that decides what a value means is a judgement call, so here is every one this tool makes.

Which convention a column uses

Decided from the column, not from your browser's language. One row that can only be read one way — 1.234,56 for numbers, 25/12/2026 for dates — settles it for every ambiguous row in that column. If nothing settles it, the tool says so and shows how many rows are affected. If two rows settle it opposite ways, the column is flagged as mixed and the contradicting rows are left as pasted rather than guessed at.

Columns it is not sure about

Left exactly as pasted. A column is only converted when a clear majority of its non-blank rows parse, so one stray 'N/A' among four hundred amounts does not block the column, but a column that is half text is not guessed at. A wrong conversion is worse than none, because it looks plausible and destroys the original in the same move.

Values starting with a zero

Treated as identifiers, not numbers, and passed through untouched. 00123 and 007 are product codes, cost centres and branch numbers; converting them drops the zeros and the code is gone. 0.5 and 0 are ordinary numbers and are converted normally.

Precision

Digits are never routed through a floating-point number. Separators are moved around the digits you pasted and the digits themselves are untouched, so 1000.00 keeps its cents and an 18-digit account number stays exact — both of which a conversion built on JavaScript's Number would silently damage.

Negatives

Three conventions, all recognised: a leading minus, accounting brackets (1.234,56), and a trailing minus 1.234,56- as written by SAP and most older ERP systems. The Unicode minus − is read as a minus too. The column report counts how many were read each way, so you can check the total against what you expected.

Thousands separators

Beyond the comma and the point: the no-break space and narrow no-break space used in France and Switzerland, the thin space, and the Swiss apostrophe in 1'234.56. Grouping is also checked rather than simply deleted, so a malformed value like 1.23.4567 is reported as unreadable instead of quietly becoming 1234567.

Indian grouping

The lakh and crore system groups in twos above the hundreds — 1,23,45,678 — and is read correctly rather than rejected as malformed.

Currency

Stripped and reported, whichever side it sits on. €1.234,56 and 1.234,56 € are the same value written by two countries, and a leading ISO code like USD 1,234.56 is common in system exports.

Percentages

The % is kept and the digits are left alone — 12,5% becomes 12.5%. They are not divided by 100, because a spreadsheet's percent cell format already does that, and doing it here as well would apply it twice.

Spreadsheet date serials

A bare number between 20,000 and 80,000 is a valid date serial — 46233 is 30 July 2026 — but it is also a perfectly ordinary quantity. So a serial alone never makes a column dates; something date-shaped has to be present. If your column really is bare serials, set it to Date by hand and they are converted.

Two-digit years

00 to 68 is read as 2000–2068, and 69 to 99 as 1969–1999 — the standard pivot. A year written with one or three digits is not a convention anywhere and is treated as unreadable rather than expanded into something plausible.

Times

Discarded. A date with a timestamp on it is converted as that whole day.

Everything else

Returned byte for byte, including leading and trailing spaces, so a column you did not want touched is genuinely untouched and the result still lines up against your source.

Questions

Why does my SUM return zero?

Because the values are text, not numbers. If a column arrives as 1.234,56 and your spreadsheet is set to US conventions, it does not recognise that as a number — it stores the whole thing as a string. The cell looks completely normal, but SUM ignores it and returns 0, and AVERAGE returns a divide-by-zero error. The usual tell is that the values sit on the left of the cell instead of the right, because spreadsheets left-align text and right-align numbers. Converting the column is the fix, which is what this tool does.

What should I convert to?

Neutral, in almost every case — 1234.56 with no thousands separator, and dates as 2026-12-31. That is not a locale, it is the form every spreadsheet in every locale reads correctly without being told anything. Then apply thousands separators and a date display in the sheet's own cell formatting. The point is that grouping separators and slash-dates are a display choice, and data that carries its own formatting will eventually land somewhere that disagrees about it. The US and European targets exist for when you are feeding a system that demands one of them.

How does it know whether 1.234 is one thousand or one point two three four?

It reads the whole column rather than each value on its own, and looks for a row that can only be read one way. 1.234,56 has both separators, so the comma must be the decimal point — and that settles the convention for every other row in the column, including the ones that are undecidable by themselves. If nothing settles it, the tool says so, tells you how many rows are affected, and lets you switch. If one row proves one convention and another row proves the opposite, that is a mixed export rather than a format, and it says that too instead of converting half the column wrongly.

Will it ruin my product codes?

No, and this is the case most tools get wrong. A value like 00123 or 007 is an identifier, not a number — converting it drops the leading zeros and the code is gone. Any value starting with a zero followed by another digit is treated as text and passed through exactly as pasted, and the column report tells you how many were left alone for that reason. Whole columns of them are detected as text and skipped entirely.

My negatives are in brackets, or have the minus at the end.

Both are handled. Accounting software writes negatives as (1.234,56), and SAP and most older ERP systems write them as 1.234,56- with the sign trailing. A parser that ignores the trailing minus turns every credit into a debit silently, which is why it is worth naming: the column report counts how many values were read as negative through each convention, so you can check the number matches what you expected.

There is an invisible character between my digits.

That is almost certainly a no-break space, and it is the official thousands separator in France and Switzerland. It looks exactly like a space, is not one, and stops normal parsing dead. Narrow no-break spaces, thin spaces and the Swiss apostrophe in 1'234.56 are all recognised as grouping and removed.

It read a column of numbers as dates, or the other way round.

Change it in the column list — every column has a type you can override, and setting one to 'Leave alone' returns it exactly as pasted. Two rules make this rare. A column is only converted when a clear majority of its rows parse, so a column the tool is unsure about is left alone rather than guessed at. And a bare number is never enough on its own to make a column dates: 46233 is a valid spreadsheet date serial for 30 July 2026, but it is also a perfectly ordinary quantity, so something date-shaped has to be present before the column is treated that way.

Are my numbers rounded or changed?

No. The digits are never converted to a floating-point number, which is the usual source of quiet damage: an 18-digit account number loses its last digits in a double, and 1000.00 comes back as 1000 with the cents gone. This tool moves the separators around the digits you pasted and leaves the digits themselves alone, so trailing zeros survive and a value of any length is exact.

Is my data uploaded anywhere?

No. Everything runs inside your browser tab using JavaScript. There is no server to send it to — this site is static files only, with no backend and no database. You can confirm it by opening your browser's network tab: nothing is sent while you work. That also means the tool keeps working if you go offline after the page loads, which is the easiest way to prove it.