How to Fix Dates in Excel and CSV Files (Mixed Formats)

Dates are the field where spreadsheets fail most quietly. A wrong phone number looks wrong. A wrong date looks like a date, sorts like a date and ruins a report three weeks later. This guide explains why dates break, how to repair the common patterns, and how to prevent the damage from coming back.

Why dates break

Excel does not store a date as text. It stores a serial number: the count of days since a starting date, formatted to look like a date. In the default 1900 date system, serial number 45000 is 15 March 2023 and 45292 is 1 January 2024. Time is the fraction of a day. Excel also supports a 1904 date system (historically used on the Mac), and Microsoft documents that the two systems differ by 1,462 days, so a date copied between workbooks with different systems can shift by four years and a day.

That design is fine until a date arrives as text, which is what every CSV file contains. A CSV has no date type; 03/04/2024 is just characters. When Excel opens the file it guesses how to read them using your computer's regional settings. The same file therefore produces different dates on different machines: 3 April in most of the world, 4 March in the United States.

Diagnose in 30 seconds

• Alignment. Real dates align right by default. Left-aligned "dates" are text.
• Test formulas. =ISNUMBER(A2) returns TRUE for real dates and FALSE for text. =ISTEXT(A2) is the opposite.
• Change the format. Select the cell and set the number format to General. A real date turns into a number like 45000. Text stays as it is.
• Look for mixed patterns. Filter the column and scan for different separators (/, -, .), month names, two-digit years and impossible values.

Fix 1: Text to Columns (fast, for one consistent order)

1. Select the column of text dates. Make sure the column to its right is empty or irrelevant.
2. Choose Data, Text to Columns. Click Next twice, leaving the delimiter options unticked.
3. Under Column data format choose Date and select the order the text is written in: DMY, MDY or YMD.
4. Click Finish, then format the column as yyyy-mm-dd (see below).

This works well when every date in the column uses the same order. It does not help when the order is mixed.

Fix 2: Build the date with DATE() from its parts

When the text has a fixed layout, take it apart and put it back together. Assume the text is in A2:

These formulas assume fixed positions (two-digit day, two-digit month). If the day or month may be one digit (5/3/2024), find the separators with FIND("/",A2) instead of counting characters, or normalize first.

For text like 5 Mar 2024, =DATEVALUE(A2) works when the month name matches the language of your Excel. If it returns #VALUE!, the month name is in another language or the pattern is unusual; use the table approach above.

The two-digit year trap

When a year has two digits, Excel decides the century for you. Microsoft documents the rule: 00 to 29 become 2000 to 2029, and 30 to 99 become 1930 to 1999. So 12-jun-24 is 2024, but 12-jun-45 becomes 1945, which is almost certainly wrong for a contract, delivery or renewal date. Wherever you can, convert to four-digit years yourself, as in the last formula above (which assumes the 2000s: change 2000 to 1900 for older data), and decide the century from what you know about the data, not from a default.

The real problem: 03/04/2024

If a file contains 03/04/2024, both 3 April and 4 March are valid dates. No formula can know which one the author meant. Guessing is how a booking, a birthday or an invoice date ends up wrong by a month.

What you can do:

1. Ask where the file came from. Systems from the US usually write month first; most others write day first. If you can re-export from the source with ISO dates, do that.
2. Let the data prove it. If any date in the column has a first number above 12 (for example 25/03/2024), the first number is the day for the whole column. If any has a second number above 12, the second is the day. Helper columns make this checkable:

   B2: =--LEFT(A2,FIND("/",A2)-1)
   C2: =--MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1)
   =MAX(B2:B500)    =MAX(C2:C500)

   If MAX of the first column is above 12 the format is day-first. If MAX of the second is above 12 it is month-first. If both are 12 or less, the column is genuinely ambiguous.
3. Flag what you cannot resolve. For rows that are ambiguous, record both readings and send them back for a decision instead of choosing one.

Silent roll-overs

DATE() is forgiving. =DATE(2024,2,31) does not return an error; it returns 2 March 2024, because it counts 31 days forward from the start of February. A typo in a day or month can therefore pass straight through a conversion formula and look perfectly valid. Always round-trip the result:

=IF(AND(DAY(D2)=B2,MONTH(D2)=C2),"ok","impossible date")

Here D2 is the converted date and B2, C2 are the day and month you extracted from the text. Any row that does not read "ok" needs a person.

When you see numbers like 45000

That is a real date in General format. Select the column, press Ctrl+1, choose Date or Custom and apply a format. If a CSV contains such numbers as text, convert them with =VALUE(A2) first, then format. If the numbers have decimals (45000.5) they include a time of day.

Store and share dates as ISO 8601

ISO 8601 writes dates as YYYY-MM-DD: year, month, day. The standard exists to remove exactly the doubt discussed above. It cannot be misread as day-first or month-first, it sorts correctly even as plain text, and databases, CRMs and import tools accept it.

• Safest way in Excel: keep the real date value and apply a custom number format. Select the cells, press Ctrl+1, choose Custom and type yyyy-mm-dd. The format codes in this dialog work in English-language Excel and travel with the workbook.
• Text version: =TEXT(A2,"yyyy-mm-dd") gives text you can paste into a CSV. Note that format codes inside TEXT depend on the language of Excel: German Excel, for instance, expects JJJJ-MM-TT. The custom format avoids that problem.
• Exporting: before saving as CSV, convert the date column to text in ISO form so the receiving system sees the same thing you see.

Two Excel quirks worth knowing

• The 1900 leap year. For compatibility with older spreadsheets, Excel treats 1900 as a leap year, so it contains a 29 February 1900 that never existed. Only dates before March 1900 are affected, but it explains oddities if you work with very old dates.
• Two date systems. If dates look four years and a day off after copying between workbooks, check File, Options, Advanced, Use 1904 date system in both files.

Date cleaning checklist

1. Copy the file; keep the original column.
2. Test with ISNUMBER to separate real dates from text.
3. Decide the reading order from the source or from the data (MAX test), not from a guess.
4. Convert with Text to Columns or DATE(); use four-digit years.
5. Round-trip each result to catch roll-overs.
6. Flag rows that are truly ambiguous and resolve them with the source.
7. Format as yyyy-mm-dd and export that form.