Mystical_Ice
asked on
How do i convert these from text to date??
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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),
Next Cell
End Sub
Kevin
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
=DATE(MID(A2,7,4),MID(A2,4
Kevin