CSV Date and Number Fixer
Choose the columns that hold dates, numbers or Excel serial numbers and what they should look like. Currency signs, thousands separators and negative brackets are cleaned out.
Drag a file here or choose one
Worked examples
Dates to one format
5/3/2024 17 Nov 2022 2021-06-30 31/02/2024
2024-03-05 2022-11-17 2021-06-30 31/02/2024 (marked as unreadable)
Day first, written as yyyy-mm-dd. 31 February does not exist.
Amounts to plain numbers
£1,234.50 (250.00) 12%
1234.50 -250.00 12
UK number style.
About CSV Date and Number Fixer
A column of dates typed by different people can hold 5/3/2024, 17 Nov 2022 and 2021-06-30 all together, and a column of money can hold £1,234.50, 1234.5 and (250.00). Sorting, adding and importing all go wrong. Choose the column, say what it holds, and every readable value is rewritten in one form: dates in the format you choose, numbers as plain numbers without currency signs, spaces or thousands separators.
Dates are checked against the calendar, so 31/02/2024 is reported rather than accepted. For dates written with numbers you say whether the day or the month comes first, though a first number above 12 can only be a day. Numbers can use either style, with commas for thousands and a point for decimals or the other way round, or you can let the tool work it out. Values that cannot be read can be left alone, emptied or marked with #CHECK so you can find them. Columns of spreadsheet serial numbers such as 45000 can be turned into dates too.
How to use CSV Date and Number Fixer
- Paste your CSV or open a file.
- For each column to fix, choose it and say whether it holds dates, numbers or Excel serial numbers.
- Choose the date format, the order of numeric dates and the number style.
- Decide what happens to cells that cannot be read, then download the result.
Common uses
- Making dates from several sources sort properly
- Removing £ signs and commas from amounts before analysis
- Turning (250.00) into -250.00
- Converting spreadsheet serial numbers to readable dates
Frequently asked questions
How are two digit years read?
Years 00 to 69 are read as 2000 to 2069, and 70 to 99 as 1970 to 1999. So 5/3/69 is 2069 and 5/3/70 is 1970.
How does it tell day first from month first?
You choose the order for numeric dates. If the first number is above 12 it can only be a day, so 17/03/2024 is read correctly whichever order you choose.
What do brackets and a trailing minus mean?
Both mean a negative number. (250.00) and 250.00- both become -250.00.
What does the number style do?
UK style reads 1,234.50 as 1234.50. European style reads 1.234,50 the same way. Work it out looks at which mark comes last, and treats a single comma followed by three digits as a thousands separator.
What happens to percentages?
By default 12% becomes 12. Tick Turn 12% into 0.12 to divide percentages by 100.