Excel : Convert text field into dates field

I have a spreadsheet which was generated by a powershell script. For some reason it has formatted some of the dates as General and some as Custom. I need them all formatted as dates.

All the fields contain text similar to this:

12/30/2014 11:08:27

I want to be able to sort the sheet by dates. I'm not too concerned about the time part of the data which could be removed if that helps.
LVL 1
roy_battyDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

ProfessorJimJamMicrosoft Excel ExpertCommented:
in the next column put =INT(cell) copy down format the cells as short date
Ejgil HedegaardCommented:
Another method.
Select the range.
Use "Text to columns" on the Data tab
Next
Select Delimited
Next
Select Space as delimiter
Next
Set the first column to the correct date format.
Finish, and you will have 2 columns with the date and the time.
ProfessorJimJamMicrosoft Excel ExpertCommented:
see the attached file.
Book1.xlsx
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

kulboyCommented:
You will need to convert them to true date values (non-text) so that they can be formatted.
You can do this by using a formula to do the conversion.
If you have a text date in cell A1 in the format dd/mm/yyyy, then you can use the following formula:

=DATE(VALUE(RIGHT(A1,4)), VALUE(MID(A1,4,2)), VALUE(LEFT(A1,2)))
ProfessorJimJamMicrosoft Excel ExpertCommented:
If the data is as text and not as dates then you can use Datevalue function to convert them to real dates
[ fanpages ]IT Services ConsultantCommented:
...or change the PowerShell script to export dates in a format similar to DD-Mmm-YYYY.
roy_battyDirectorAuthor Commented:
none of these seem to work.

the =int(cell) just gives me VALUE

the second suggestion works until I get to the set cell format bit. I have cell all cells in the column to be numbers\date. I still have the problem where some dates are left justified and some right.

This - =DATE(VALUE(RIGHT(A1,4)), VALUE(MID(A1,4,2)), VALUE(LEFT(A1,2))) just changes the date to another date.

I didnt write the powershell script so not sure how to amend it.
[ fanpages ]IT Services ConsultantCommented:
I didnt write the powershell script so not sure how to amend it.

You could give us a clue by posting it within a following comment :)
Ejgil HedegaardCommented:
the second suggestion works until I get to the set cell format bit. I have cell all cells in the column to be numbers\date. I still have the problem where some dates are left justified and some right.

That is because some was text (left) and some numbers (right).
Set all to Standard.
kulboyCommented:
if you have dates, then you can order by date?  Wasn't that the goal?
[ fanpages ]IT Services ConsultantCommented:
This - =DATE(VALUE(RIGHT(A1,4)), VALUE(MID(A1,4,2)), VALUE(LEFT(A1,2))) just changes the date to another date.

if you have dates, then you can order by date?  Wasn't that the goal?

With the first quoted statement, I am presuming roy_batty meant that a different date was the result; perhaps the day (number) & month (number) were transposed so that, for example, 9 January 2015 becomes 1 September 2015 (09/01/2015 to 01/09/2015).
frankhelkCommented:
Simple Trick that often worked for me:

Select the complete range of fileds
Change the cell formatting to "Date" with your desired format
With the range still selected: CTRl-C
Edit -> Insert As -> Values only

That should help.
roy_battyDirectorAuthor Commented:
Im still struggling with this one. I have attached the file minus a couple of confidential columns.

Can anyone help?
files.xlsx
Ejgil HedegaardCommented:
Use this formula in B2

=IF(A2="","",IF(ISNUMBER(A2),A2,DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2))))

Then select the values in column B, copy and paste special, values.
 
See file
files-1.xlsx

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
kulboyCommented:
can you provide the original export?
roy_battyDirectorAuthor Commented:
Perfect. Many thanks.
kulboyCommented:
can you provide me the original export?
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.