Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How do i convert these from text to date??

Posted on 2011-03-22
Medium Priority
257 Views
Hi,
Using Excel 2007; i've imported a huge CSV file into Excel.

There is a column with thousands of 'date' entries, but for some reason Excel is not seeing them as dates.

They are entered like this:

19/01/2011 02:45:01 pm
19/01/2011 02:42:35 pm
19/01/2011 02:40:14 pm
19/01/2011 02:39:23 pm
19/01/2011 02:39:08 pm
19/01/2011 02:34:52 pm
19/01/2011 02:29:55 pm
19/01/2011 02:20:19 pm

I'm trying to sort them by date in a pivot table, but they won't sort correctly, because Excel doesn't see them as dates.

How do I convert them to date fields?  I don't need the 'second', but I do need the day, month, year, and then the time (03:50, 04:01, etc)

Any suggestions?
0
Question by:Mystical_Ice
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 1600 total points
ID: 35193000
Try this formula on the dates in a separate column:

=DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))

Kevin
0

LVL 81

Expert Comment

ID: 35193017
To include the time:

=DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))+TIMEVALUE(MID(A2,13,11))

Kevin
0

LVL 81

Expert Comment

ID: 35193079
A macro that will convert the selected dates:

Public Sub CleanDates()

Dim Cell As Range

For Each Cell In Selection
If Left(Cell, 1) = Chr(160) Then Cell = Mid(Cell, 2)
Cell = DateSerial(Mid(Trim(Cell), 7, 4), Mid(Trim(Cell), 4, 2), Left(Trim(Cell), 2)) + TimeValue(Mid(Trim(Cell), 13, 11))
Next Cell

End Sub

Kevin
0

LVL 50

Assisted Solution

barry houdini earned 400 total points
ID: 35193208
You can use "Text to columns" functionality

Select column of dates

Data > Text to columns > Next > Next > under "column data format" select "date" and the source format from the dropdown, i.e. DMY > Finish

Then you can format them any way you want

regards, barry
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month7 days, 3 hours left to enroll