Cannot change some cells to date format in Excel

Data -> Text to Columns ->Fixed width -> Next -> Next -> select Date as MDY -> Finish

Above shall work. Critical part is selecting of MDY (source format)

Hi jlockley​,

Assuming that your computer has the date in the format dd/mm/yyyy and if some dates are in the correct format, i.e. dd/mm/yyyy and some others are given in the format mm/dd/yyyy then you can use the following formula to have all dates in the format dd/mm/yyyy.

=--REGEXREPLACE(A7:A33, "^(\d{2})/(\d{2})/(\d{4})$", "\2/\1/\3")

Select all the data that comes out of the formula and go to Format Cells (use CTRL + 1 from the keyboard) and under Category select Date and then OK.

The formula is for Excel 365 or Excel for the web.

IlirU

Great observation from the previous user. You’re right that the issue is a mismatch between the bank’s US format (mm/dd/yyyy) and your Excel’s date system (likely dd/mm/yyyy).

The formula they shared will work if every date is strictly mm/dd/yyyy with two-digit month, two-digit day, and leading zeros (e.g., 01/05/2025). However, it can break if there are single‑digit days/months (1/5/2025), spaces, or if the cell already contains a real Excel date.

Here’s a additional try with the formula you can use too. It assumes your first problematic date is in cell A1 (adjust the row number as needed):

=LET(

  raw, TRIM(A1),

  DATE(

    VALUE(RIGHT(raw, 4)),

    VALUE(LEFT(raw, FIND("/", raw) - 1)),

    VALUE(MID(raw, FIND("/", raw) + 1, FIND("/", raw, FIND("/", raw) + 1) - FIND("/", raw) - 1))))

This works even if months/days have no leading zeros.

Even simpler:
Use Text to Columns — no formulas needed:

1. Select the column with the dates
2. Go to Data → Text to Columns → Delimited → uncheck all boxes → Next
3. Under Column data format, choose Date: MDY → Finish

That forces Excel to read mm/dd/yyyy correctly in one click.