You may try Selecting that column, text to column>Next>Next>Choose date >MDY. It will convert text to date value. Then format will work on it.
Can't change the date formatting
excel date formatting not working - Stack Overflow
Excel Date formatting not applying at all
Date Format - Not Changing | Chandoo.org Excel Forums - Become Awesome in Excel
Videos
You may try Selecting that column, text to column>Next>Next>Choose date >MDY. It will convert text to date value. Then format will work on it.
Hi, thanks for your reply. Unfortunately, after following the steps nothing changed when I pressed finish and selected Long Date for the column. If I click on one of the locked date entries for example, the type shows as "date" in the drop-down and no matter what I pick, they do not change. Any ideas?
Try specifying a custom number format of mm-dd-yy, that should do the trick.
Incidentally, General is a number format that can cause headaches sometimes - Excel decides for itself what type of value it thinks you entered in a cell. I tend to stay away from General and explicitly pick my number formats where possible.
I did not want to have to use formulas for a simple table where I would be entering task start and finish dates. My frustration was relieved when I realized that the issue was with my Windows 10 Language & region settings which Excel uses to determine default date formats.
I navigated to Settings -> Time & language -> Language & region -> Regional format
I could see that my "Short date" format was using European dates, while the "Long date" was using US formats. I clicked the "Change formats" button and updated my short date format to the US format. Once I did this all was right with the world.
The following worked for me:
- Select the date column.
- Go to the Data-tab and choose "Text to Columns".
- On the first screen, leave radio button on "delimited" and click Next.
- Unselect any delimiter boxes (everything blank) and click Next.
- Under column data format choose Date
- Click Finish.
Now you got date values
Given your regional settings (UK), and the inability of formatting to change the date, your date-time string is text. The following formula will convert the date part to a "real" date, and you can then apply the formatting you wish:
=DATE(MID(A1,FIND(",",A1)+1,5),MATCH(LEFT(A1,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),MID(SUBSTITUTE(A1,","," "),5,5))
Might be able to simplify a bit with more information as to the input format, but the above should work fine. Also, if you need to retain the Time portion, merely append:
+RIGHT(A1,11)
to the above formula.
1.png800×642 287 KB
Please review the attached screenshot. I have a spreadsheet where someone typed on all of the dates seen on column B. I want to change all of these dates to the format mm/dd/yy. So I chose cell B17 as in the screenshot, right click, chose Format Cells, selected Date and chose my desired format. However, as seen, even the “Sample” is not reflecting the desired output and if I select OK, the new formatting is not applied to the cell. What am I doing wrong or not understanding? My wish is to convert all of these dates to my desired formatting without typing all of this over. Was hoping to use a format painter.
Please advise.
Thank you 
I wonder if the field is a text field? Were there spaces entered before or after the numbers? Or a single quote mark forcing Excel to treat it as a literal string?
Strange that the sample doesn’t show correctly though.
What about your system regional preferences?
AND BTW, if you’re going to take the trouble to change the date format, change it to the logical, non-ambiguous, YYYY-MM-DD. Do it now so you won’t have to do it again in a few years. Change all your other programs and systems. Change you phone and your systems at home. Just do it.
ISO 8601
ISO
ISO - ISO 8601 — Date and time format
ISO 8601 is the internationally accepted way to represent dates and times.