Excel text to convert date format

Question

I have a batch of data with several columns which the date is formatted like this:

May-31-2014.

I'm trying to figure out a way to convert that into a useful date format 05/31/2014. Any ideas?

Answer 1

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.

1. Splitting out the month:
   This is achieved using the search function to find the first - and the splitting tool Left to cut it out.

   =LEFT(A1, SEARCH("-",A1,1)-1)

   which returns May from your example.

2. 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:

   =MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)

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

3. Splitting out the year:
   The last tool we need is the RIGHT tool.

   =RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1))

   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.

4. 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"))

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

6. 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))

Answer 2

Another simple approach is to take a string like May-31-2015 and convert it into 31-May-2015. This conversion will allow us to use the DATEVALUE() formula.

So with data in A1, in B1 enter:

=DATEVALUE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","") & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))

Answer 3

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:

=DATEVALUE(CONCATENATE(MID(A1,5,2),"-",LEFT(A1,3),"-",RIGHT(A1,4)))

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)

Thanks!