Skip to main content
SheetTidy

Fix mixed and broken dates in Excel and CSV files

Turn text dates, serial numbers and swapped day/month values into real dates in one consistent format, without guessing.

  1. 1
  2. 2
  3. 3
  4. 4

Drop your Excel or CSV file here

XLSX, XLSM, XLS, ODS, CSV, TSV or JSON

Your file never leaves your device

How to use this tool

  1. Step 1: Open your file

    Drop an Excel or CSV file onto the tool. The file check points out dates stored as text or as plain numbers.

  2. Step 2: Choose how to read 03/04/2024

    Pick Day/month (3 April, used in India, the UK and most countries) or Month/day (March 4, used in the US). Dates like 15/03/2024 are always read correctly.

  3. Step 3: Pick a display format

    Show dates as DD/MM/YYYY, MM/DD/YYYY, YYYY-MM-DD or DD-MMM-YYYY. For Excel files, choose real Excel dates or text.

  4. Step 4: Review and download

    Fixed cells are highlighted, and the summary lists any cells that could not be read as dates with their cell address. Download when it looks right.

Why dates break in Excel

Excel does not store a date as the characters you see. It stores a whole number, the count of days since 1 January 1900, and then formats it. 45366 is 15 March 2024; the format decides whether that shows as 15/03/2024, 03/15/2024 or 15-Mar-2024. Problems start when a value never becomes that number, or becomes the wrong one.

  • The CSV was read with the wrong regional setting. When you open a CSV, Excel interprets dates using the short date format in Windows Settings › Time & language › Language & region › Regional format. A file written as day/month opened on a computer set to month/day (or the reverse) is misread.
  • Dates were pasted or imported as text. Values copied from a web page, a PDF or another system often arrive as plain text. Excel aligns text to the left by default, so a column of left-aligned dates is a good warning sign.
  • Serial numbers appear instead of dates. If a date column is formatted as General or Number, you see 45366 rather than a date. This often happens after copying values between workbooks or exporting from a database.
  • Several systems, several styles. A merged sheet can hold 2024-03-16, 18 Mar 2024, March 19, 2024 and 20240420 in the same column.

The “half my dates are wrong” problem

This is the most confusing case. Suppose a file uses day/month but your Excel reads month/day. 03/04/2024 (3 April) is silently stored as 4 March, a real date, but the wrong one. 15/03/2024 cannot be month 15, so Excel leaves it as text. The column looks partly fixed and partly broken, and the dates that look correct are the dangerous ones, because nothing marks them as swapped.

To check a cell, enter =ISNUMBER(A2) beside it. TRUE means Excel holds a real date; FALSE means it is text.

How to fix dates manually in Excel

Serial numbers: change the format

Select the cells, press Ctrl+1 to open Format Cells, choose Date on the Number tab (or Custom and type dd/mm/yyyy), then click OK. The values were already dates; only the display changes.

Text dates: Text to Columns

This built-in feature converts a column of text dates in one pass and lets you state the order of day and month.

  1. Select a single column of dates.
  2. Choose Data › Text to Columns, pick Delimited and click Next.
  3. Clear every delimiter box and click Next.
  4. Under Column data format, choose Date and pick the order the text is written in: DMY, MDY or YMD.
  5. Click Finish, then format the column with Ctrl+1.

It works well when the whole column uses one order. Values written in other styles, such as month names mixed with numbers, may stay as text.

Formulas

=DATEVALUE(A2) turns text into a date serial, but it reads the text using your computer’s regional setting, so the same file gives different answers on different machines. For text that is always dd/mm/yyyy, this formula avoids the problem:

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

It only works when every value has exactly two-digit days and months and a four-digit year. Format the result as a date, then use Paste Values to replace the original.

Power Query

Load the data with Data › From Table/Range, right-click the column header and choose Change Type › Using Locale. Set the type to Date and the locale to one that matches the file, such as English (United Kingdom) for day/month. Values that do not fit show as errors, which at least makes them visible.

Where this tool helps

Bank statements. Downloaded statements often mix text dates with real ones, so sorting by date puts transactions out of order.

Exports from US software. Reports from American tools write month/day. Opened in Excel set up for India or the UK, they produce the swapped-date problem above.

Merging files from different systems. A CRM, a billing tool and an e-commerce platform each write dates their own way. One consistent format makes the combined sheet sortable and lets pivot tables group by month.

Upload templates. GST returns, Tally imports and payroll templates often require DD/MM/YYYY exactly. Choose Text in the Save as option when the template checks the characters rather than the value.

How the tool reads dates

  • Many styles are understood: 2024-03-15 (with or without a time), 15/03/2024, 15-03-24 or 15.03.2024, month names such as 15 Mar 2024, 15-Mar-24, March 15, 2024 or Friday, 15th March 2024, compact 20240315, and times with am or pm.
  • Two-digit years follow Excel’s rule: 00 to 29 become 2000s, 30 to 99 become 1900s.
  • Serial numbers are converted in date columns, including serials stored as text in a CSV.
  • Nothing is guessed. Impossible dates such as 31/02/2024 and words such as TBD stay as they were and are listed in the summary with their cell address.

A typical summary reads: “Fixed 9 dates in Order date and Due date, shown as DD/MM/YYYY. 2 were Excel serial numbers and 1 date could be read either way and was read as day/month.” If that last count is high, double-check that the day/month setting matches where the file came from.

Frequently asked questions

How does the tool decide between day/month and month/day?

A date such as 15/03/2024 can only mean 15 March, and 03/15/2024 can only mean March 15, so those are always read correctly. Only dates where both parts are 12 or less, like 03/04/2024, follow your setting, and the summary tells you how many there were.

What happens to values like 31/04/2024 or TBD?

They are left exactly as they are. April has no 31st day, and TBD is not a date, so the tool does not guess. The summary lists each one with its cell address, for example D5 “31/04/2024”, so you can fix it by hand.

Why does my date show as a number like 45366?

Excel stores dates as the number of days since 1900, and 45366 is 15 March 2024. The cell is formatted as General or Number, so you see the raw count. The tool turns these serial numbers into dates in a date-like column.

Should I save as real Excel dates or as text?

Real Excel dates are best for most work: you can sort, filter and calculate with them, and they show in your chosen format. Choose Text only when an upload template insists on the exact characters, such as 05/09/2024.

Can I fix dates in a CSV file?

Yes. A CSV has no date type, so dates are written as text in the format you choose. Serial numbers stored as text in a CSV are recognised too.

Which columns are changed?

By default, columns where at least half the values read as dates, or with a header such as Date, Joined or Due over dates or plausible serial numbers. You can also pick the columns yourself. The chosen format is applied to every date in the downloaded sheet.

Related tools

All fix tools