Yeraldin Mueses

Posted on Oct 20

How to Fix Mixed-Format Dates in Excel and Google Sheets (DD/MM vs MM/DD, Text Dates)

#googlesheets #excel #productivity #tutorial

You open a CSV that someone exported from a UK or European system, and the date column is a mess. Some cells are right-aligned, some are left-aligned. 15/04/2024 sits there as text. 03/04/2024 looks fine but is now March 4 instead of April 3. Sorting and filtering are broken, and a pivot by month is wrong without any warning.

Here's what happened and how to fix it in Excel and Google Sheets.

Why this happens

The file was written in DD/MM/YYYY order, and your spreadsheet runs in a MM/DD/YYYY locale (for example, US English). When the file opens:

Any value where the day is 12 or less, like 03/04/2024, looks like a valid US date, so it gets converted to a real date with day and month swapped.
Any value where the day is over 12, like 15/04/2024, can't be a US date, so it stays text.

So one column can hold wrong real dates and unconverted text at the same time. Changing the cell format won't fix either kind.

Step 0: Diagnose the column

Real dates are numbers underneath. Put this next to your data (assume dates are in column A, starting in row 2):

=ISNUMBER(A2)

TRUE means it's a real date (possibly swapped). FALSE means it's text. This works the same in Excel and Google Sheets.

If every cell is FALSE (all text), skip to the Text to Columns or Power Query options. They're cleaner. The formula below handles the mixed case.

Option 1: The formula fix (mixed real and text dates)

This formula assumes the original data was DD/MM/YYYY with a 4-digit year, and that every real-date cell in the column got there by the swap described above. In a helper column B:

=IF(ISNUMBER(A2),
    DATE(YEAR(A2), DAY(A2), MONTH(A2)),
    DATE(RIGHT(A2,4),
         MID(A2, FIND("/",A2)+1, FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1),
         LEFT(A2, FIND("/",A2)-1)))

What it does:

If the cell is a real (swapped) date: it rebuilds the date with day and month switched back. DATE(year, month, day) takes the old day as the month and the old month as the day.
If the cell is text: it splits on the slashes. LEFT up to the first / is the day, MID between the two slashes is the month, and RIGHT(…,4) is the year. Using FIND instead of fixed positions means 1/2/2024 and 01/02/2024 both work.

Fill it down, format column B as a date (I suggest yyyy-mm-dd so nothing is ambiguous), then copy column B and Paste Special → Values over the original if you want to replace it.

This works as written in Excel (any modern version) and Google Sheets.

Excel 365 / Google Sheets tidier version for an all-text column using LET:

=LET(s, A2,
     a, FIND("/", s),
     b, FIND("/", s, a+1),
     DATE(RIGHT(s,4), MID(s, a+1, b-a-1), LEFT(s, a-1)))

LET needs Excel 365 or Excel 2021 or later. Google Sheets supports it too.

Watch out:

Don't use the swap branch if some real dates in the column were already correct, for example typed in by hand. The formula can't tell them apart, and you'd swap good dates. In that case, re-import the source file (Option 2 or 3).
If your text uses - or . instead of /, use SUBSTITUTE(A2,"-","/") in place of A2, or change the FIND character.
Two-digit years (03/04/24) need 2000+RIGHT(A2,2) instead of RIGHT(A2,4).

Why not just DATEVALUE?

DATEVALUE converts text to a date using your current locale. In a US-locale workbook, DATEVALUE("15/04/2024") returns #VALUE!, and DATEVALUE("03/04/2024") gives you March 4, which is the same wrong answer. It's useful when the text is already in your locale's order or in ISO format (2024-04-15), but it can't fix DD/MM text in an MM/DD workbook.

In Google Sheets you can get around this. Go to File → Settings → Locale, set it to a DD/MM country like United Kingdom, and =DATEVALUE(A2) will read text cells as DD/MM. The locale change doesn't fix cells that were already converted wrongly, so you still need the swap branch for those.

Option 2: Text to Columns (Excel, when the column is all text)

If the column is still all text (for example, you imported it with the column set to Text), Excel's built-in converter is quick:

Select the date column.
Data → Text to Columns.
Choose Delimited, click Next, uncheck all the delimiters, and click Next.
Under Column data format, choose Date and pick DMY from the dropdown.
Click Finish.

Excel reads each text value as day-month-year and turns it into a real date. Use it on text cells. Cells that were already converted into swapped real dates still need the formula.

Google Sheets' Data → Split text to columns has no DMY option. Use the locale and DATEVALUE approach, or the formula above.

Option 3: Power Query (Excel, the best fix if you still have the original file)

The cleanest fix is to stop Excel from guessing in the first place:

Data → Get Data → From File → From Text/CSV, and pick the original file.
Click Transform Data.
Right-click the date column header and choose Change Type → Using Locale…
Set Data Type to Date and Locale to the source country (for example, English (United Kingdom)).
Click OK, then Close & Load.

Power Query reads the raw text with the rules you chose, so nothing gets swapped. Next month, drop in the new file and click Refresh. Power Query is built into Excel 2016 and later on Windows and into Microsoft 365 for Mac. Older Windows versions need the free add-in.

Step 4: Check your results

Don't trust it until you've checked it. With the fixed dates in column B:

=COUNT(B2:B500)                         → how many real dates you have
=COUNTA(A2:A500)                        → how many non-blank source cells
=SUMPRODUCT((A2:A500<>"")*NOT(ISNUMBER(B2:B500)))  → filled rows that still are NOT real dates (want 0)
=TEXT(MIN(B2:B500),"yyyy-mm-dd")        → earliest date: does it make sense?
=TEXT(MAX(B2:B500),"yyyy-mm-dd")        → latest date: does it make sense?
=COUNTIF(B2:B500,">"&TODAY())           → any dates in the future?

The first two numbers should match, and the third should be 0. A minimum or maximum that's way off, or unexpected future dates, usually means some rows went through the wrong branch.

One more thing no formula can solve: if the source mixed DD/MM and MM/DD rows, a value like 03/04/2024 really is ambiguous. To see how many rows could go either way, count the fixed dates where the day is 12 or less:

=SUMPRODUCT(--(DAY(B2:B500)<=12))

For those rows, check another column (an invoice number, the order of entries, the source system) or ask whoever exported the file.

What I tested

I built a sample column in LibreOffice Calc (headless, en-US locale) with the same mix you get from a DD/MM file opened in a US locale. It had 03/04/2024, 1/2/2024, 12/11/2024, 9/10/2023 and 05/06/2024 stored as swapped real dates, and 15/04/2024, 31/12/2023 and 28/02/2024 left as text. The main formula returned the correct date for all 8 rows. The LET version and the text-only branch also got all 8 right, and the check formulas returned the expected counts. I couldn't test the Excel Text to Columns or Power Query steps or Google Sheets itself here, so those steps follow the standard menus. If a menu name differs in your version, look for the same option nearby.

Disclosure: I run Spreadsheet Fix in 48h, a paid service. If you'd rather hand it off, fixing a date column like this on one tab is the $29 Fix option. You get a full refund if it's not back in 48h: https://yeraldinmueses.gumroad.com/l/spreadsheet-fix

Thanks for reading. Questions or edge cases are welcome in the comments.

Yeraldin

Drafted with AI assistance; formulas tested in LibreOffice Calc as described above.

DEV Community