I have a batch of data with several columns which the date is formatted like this:
I'm trying to figure out a way to convert that into a useful date format 05/31/2014. Any ideas?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
There are a good few steps to achieving this. This is one way to do it using formulas and helper cells. If you need to do this without helper cells (i.e. you do not want extra columns) then you should mention this and I could probably work up some VBA to do the task.
The first task is to split your date out into 3 parts: the year, the month, the day. Once you have those three then it's simple to piece it back together.
This is of course assuming that your dates always follow the Rather Idiotic American Date Format© of MMM-dd-yyyy.
This was done using the Microsoft page Split text among columns by using functions as an example.
Splitting out the month:
This is achieved using the
search function to find the first
- and the splitting tool
Left to cut it out.
May from your example.
Splitting out the day:
Is similar but a much more complicated use of the
MID function is required. We need to find the first and second
- then subtract the location of the first from the second to get the length of the text required this results is a rather long function:
SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1) returns the length while the
SEARCH("-",A1,1)+1 is simply getting the first location. the
MID tool cuts them out and returns
23 from your example.
Splitting out the year:
The last tool we need is the
This simply gets the length of the string and then searches through it twice and returns everything that is left (or right).
The problem is now that you still have the month in an unusable text format. To make is usable we need to use the
DATEVALUE tool to convert it. This tool wont accept a month on its own, but will accept a
MMM YY string. What we can do is then parse our month text into a number after we concatenate a year onto it.
Getting the Month as a numerical value:
For the sake of argument lets say we stored out first function in cell B1.
DATEVALUE(B1 &" 01") will return the date value string that excel uses internally, we can then wrap that in a
MONTH function to return the numerical value for the month:
MONTH(DATEVALUE(B1 &" 01"))
We can then merge all these into one
DATE(year,month,day) function and thus convert it into a real date that Excel can use. The month is in cell B1, the day in cell C1, and the year in D1 following the Stupid American Date Format™:
DATE(D1, B1, C1)
will then return
23/05/2014 in the cell.
If you want to get really fancy you can go ahead and combine all of the above formulas down into one.
=DATE(RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1)),MONTH(DATEVALUE(LEFT(A1, SEARCH("-",A1,1)-1)&" 1")),MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1))
I know the question has been answered, and I appreciate all the inputs. I would like to add this other option just in case is helpful to anyone:
This will convert the date from the May-31-2014 format to 5/31/2014 format. Notice that in my specific case the months were entered with only three letter (e.g. Jul)