Date format problem

The attached file contains dates. Some will format. Some will not.
I'd like to be able to format them all.
Date-problem-example.xlsx
gregfthompsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naresh PatelFinancial AdviserCommented:
Try this in column B
=TEXT(A9,"DD/MM/YYYY")

Open in new window

gregfthompsonAuthor Commented:
Thanks.

This allows all cells to be formatted in the same way.
But it still will not allow the date format function to work to change the format.
I am seeking to be able to change the format through the date format function.
Wayne Taylor (webtubbs)Commented:
They are likely entered as Text format. To change to something that can be identified as a date, enter '1' in a blank cell. Copy that cell, select the date cells and Paste Special, with Multiply as the operation.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Because some are Text entries, they look like dates but not the real dates.
To convert them all to real dates, follow these steps..

Select column A --> Data Tab --> Text to columns --> Delimited --> Next --> Next --> Select Date radio button for column data format --> Finish.

Now you can apply all the date formats to the dates in column A.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Copy a blank cell.

Select all the dataes then PasteSpecial. Scroll don and select PasteSpecial then Add
aranaCommented:
you are having problem because of regional settings, in the sheet you gave us, what is the correct date for a16? is it dec 10 or oct 12?
 
if it is OCt 12 then use this in the next cell (b16) to convert it
=IFERROR(DATEVALUE(TEXT(A16,"MM/dd/YYYY")),DATEVALUE(MID(A16,4,2) & "-" & LEFT(A16,2)&"-"&RIGHT(A16,4)))
Naresh PatelFinancial AdviserCommented:
@arana,

i am not author but i tried and it works perfect.
=TEXT(IFERROR(DATEVALUE(TEXT(A2,"MM/dd/YYYY")),DATEVALUE(MID(A2,4,2) & "-" & LEFT(A2,2)&"-"&RIGHT(A2,4))),"DD-MMM-YY")

Open in new window


Good Comment
gregfthompsonAuthor Commented:
Thanks - works perfectly, and is simple.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I could help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.