So I have an excel file which has a date column in the format mm/dd/yyyy Like in this picture:

I want it in the format dd/mm/yyyy and i tried formatting the cells but they dont change. at last I chose to simply extract and concat the dates in a new column but it works for some cells while with others not.. this is the result:

As you can see sometimes it works and sometimes not. this is the formula I used (please bear in mind I am just starting out with excel and I have no clue of tricks or other methods) :

what is causing the faulty results in some of the cells ? TIA

Edit: Link for the data

So just select those range, Goto Data Tab --> Click On Text To Columns --> Then select Delimited in the First Step --> Next --> Next as well in the Second Step --> and in the Third Step click on Date and change it to MDY and change the destination to adjacent cell and press Finish.

Since Dates & Times are stored as Numbers in Excel hence it will return you as numbers therefore you just need to format it as dd/mm/yyyy by pressing CTRL 1 --> Format Cells dialog opens --> under Number Tab --> Click on Custom and type by removing the General --> dd/mm/yyyy.

Using TEXTJOIN() & MID() Functions, assuming you are using either Excel 2019/2021 or MS365

• Formula used in cell B2

=TEXTJOIN("/",,MID(A2,{4,1,7},{2,2,4})) 0