Fix Excel Dates With the Day and Month Swapped

If Excel imported May 4 as April 5, changing the cell format to yyyy-mm-dd will display April 5 more clearly. It will not recover the intended date. If the cell contains text instead, a date display format alone does not turn that text into a date suitable for calculations.

The repair starts with the source convention and the stored value. Return to the original CSV or an earlier query step when values have already been misinterpreted. The menu instructions below target Windows desktop Excel for Microsoft 365 and Excel 2024; Mac and web interfaces can differ.

A valid date can still be the wrong date

Consider a source that uses day/month/year. DMY and MDY can both interpret some of its strings successfully, but produce different calendar dates. The following illustrative values show why checking only error rows misses part of the problem.

When both components are at most 12, the wrong interpretation may create a perfectly valid date. A day above 12 is more likely to reveal the mismatch by remaining text or producing a conversion error. Microsoft documents this locale-dependent interpretation for Power Query.

A UK Excel Online user collecting birth dates from partner organizations reported that dates with both components at most 12 appeared reversed between participants. That observation calls for checking stored values as well as display settings; it does not establish that every affected-looking cell has the wrong value.

If the source contains only ambiguous strings and nobody knows its convention, no formula can recover the author’s intention reliably. Obtain the source specification or compare individual records with dated documents. For files combined from multiple providers, determine the convention by provider rather than assuming the whole column follows the first recognizable row.

Check the value before changing its appearance

Excel stores dates as serial numbers. Formatting controls how those numbers appear, while a text date is a string that still needs interpretation. Microsoft’s conversion instructions separate converting text into a serial number from applying a date format.

For a value in A2, put =ISNUMBER(A2) and =ISTEXT(A2) in neighboring helper cells. The IS functions inspect the value without coercing it. A numeric date returns TRUE for the first test; a text date returns TRUE for the second. Treat blanks and errors separately instead of interpreting every FALSE as text.

A TRUE result from ISNUMBER is not proof of a correct date. April 5 and May 4 are both numeric dates. Alignment is also only a clue because users can change it. Once you have identified the storage type, inspect the actual month and day against the source.

If the value already represents the right day, select the cells, press Ctrl+1 and choose a date format or a custom format such as yyyy-mm-dd. There is no reason to parse an already-correct date again simply because another person prefers a different display order.

Convert one text column with a known order

For a one-off column whose text follows one convention, Text to Columns provides a conversion route. You can keep each string in one column and specify its date order in the last step.

1. Keep the original file and work on a copy. Select only the text-date range, such as A2:A100, and leave an empty destination column.
2. Open Data → Text to Columns, choose Delimited, and select Next.
3. Clear delimiters so the preview remains one column. Do not split the date at its slashes or hyphens.
4. In the final step, choose Date and the source order: DMY for day-first strings or MDY for month-first strings.
5. Set Destination to an empty cell such as B2, finish, and compare the resulting dates before choosing their display format.

The wizard’s date order describes the input. It is not a request for the output’s visual arrangement. Selecting YMD because you want an ISO-style display will not correctly parse a DMY source.

Microsoft’s Text to Columns instructions include a destination setting, allowing the original text to remain beside the conversion. If some rows have already become incorrect numeric dates, recover their original strings first. Do not mix those rows with untouched text and assume the same operation repairs both.

Give recurring imports an explicit locale

Opening a CSV directly lets Excel interpret columns using its current defaults. For a repeatable import, use Data → From Text/CSV and choose Transform Data in the preview instead of loading immediately.

In the new query, inspect Applied Steps. Automatic detection may have added Changed Type before you specified the source convention. Select the preceding step to see whether the original strings are still present. If the automatic step introduced the wrong dates, remove that step in this new import and assign the required column types deliberately.

Each applied step displays its own result. A new locale applied after a string has already become April 5 does not restore the original May 4 interpretation. In an established query with later transformations, locate and revise the responsible conversion rather than deleting all subsequent work.

From the text column, right-click the header and choose Change Type → Using Locale. Select Date and English (United Kingdom) for a DMY source, or English (United States) for an MDY source. Validate the result, then use Close & Load. Other columns affected by removing the automatic type step also need their appropriate types restored.

A locale specified on the conversion takes precedence over the query and operating-system defaults. You can therefore keep different import conventions for different sources without switching the region of the entire computer.

Where DATEVALUE and DATE fit

=DATEVALUE(A2) converts a recognizable text date into a serial number, which can then receive a date format. However, DATEVALUE depends on system date settings. It does not supply the missing convention for a mixed DMY/MDY column. It also ignores time information, so it is unsuitable when a timestamp’s time must survive.

If every input is exactly ten characters in YYYY-MM-DD form, you can construct the date from known positions instead. For text such as 2026-05-04 in A2:

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

This takes the first four characters as the year, two characters starting at position six as the month, and the final two as the day. It is not suitable unchanged for 2026-5-4 or a string containing a time.

DATE normalizes out-of-range components: =DATE(2026,2,31) becomes March 3, 2026, rather than an invalid-date error. Compare the resulting components with the input to catch this. After verification, copy the results and paste values into a new column if you need fixed values rather than formulas.

Validate the result before sorting records

For a converted value in B2, use =YEAR(B2), =MONTH(B2) and =DAY(B2). The first DMY example must return 2026, 5 and 4. If the month is 4 and the day is 5, a successful numeric conversion has still produced the wrong date.

Check ambiguous rows as well as days above 12. Compare blank and unresolved entries, and account for every source record. Removing conversion errors can make a column look clean while dropping the orders or people represented by those rows. In Power Query, the Add Column date options can extract year, month and day for the same comparison.

Group the three illustrative records above by month to see the consequence. With a DMY source, you should have two records in May and one in December. Reading those strings as MDY puts one in April and one in November, while 13/05/2026 cannot become a valid date in that order. Delete the error and you are left with two valid dates, both in the wrong months. After converting again from the original strings, confirm both the three-record count and the expected May and December groups.

Once the values are correct, sort the table or complete record range by the converted date. Expand the selection when adjacent fields belong to the same records. Sorting only the date column can attach one order’s date to another order’s amount.

Keep the next file from undoing the repair

Save the query in an .xlsx workbook and retain the untouched source CSV separately. When the next file arrives, confirm that its column names and date convention still match before refreshing.

If you control the collection template, specify a four-digit year and a complete example, such as “YYYY-MM-DD: 2026-05-04.” For workbooks containing correct date values, agree on a readable display. For a CSV handoff, agree on how the recipient will parse those strings as well; the date formatting of your current workbook does not travel as an import instruction.