Convert date e.g. Jun 05 2016 08:00:00 to dd/mm/yyyy hh:mm:ss

Question

Got input such as in the topic title. Trying to figure out how to convert this into UK date and time to be used in calculations.

I've looked at some methods on Google such as using text to columns, but I don't think this is what I'm looking for...

Thanks!

Edit: month is always in abbreviated format.
Edit 2: I should mention that I'm in the UK, and it doesn't seem to convert US date automagically.
Edit 3: Data:

Jun 05 2016 08:00:00 to dd/mm/yyyy hh:mm:ss

Answer 1

The DATEVALUE function expects a comma between the day and year; the REPLACE function can add that in. The TIMEVALUE function should be able to read that time without modification.

=DATEVALUE(REPLACE(LEFT(A1, 11), 7, 0, ","))+TIMEVALUE(RIGHT(A1, 8))

Note that the original in A1 is left-aligned; this indicates a text value. The converted date/time in B1 is right-aligned; this indicates a true date/time value.

Column B was formatted as dd/mm/yyyy hh:mm:ss. As General it would show as 42526.33333.

Answer 2

Assuming that your source date is a string and it is in the cell D10, the first thing you need to do is convert it to an excel date time serial. In the Excel Date Time serial there are a few things to note.

• The integer portion of the number represents the number of days since Jan 1 1900 in windows and I think 1904 on mac
• The decimal portion of the number represents the time in fraction of a day. 0.5 would represent noon. Valid Excel times for VBA are 00:00 to 23:59:59. 24:00 is not a valid time, though it will work with some excel formulas

So in order to convert your string to an Excel date serial we will need to rip out the components and dump them into the DATE() function. The date function consists of three arguments:

DATE(year,month,day)

Pretty straight forward with the exception that those values need to be numbers. Why dont we start pulling your information going from the largest unit to the smallest unit.

Thankfully your string is of consistent length. You have leading 0 for your single digits so they will occupy the same space as double digits. So this method will work until the year 9999, but I don't think we are too worried about that right now.

In order to pull the year we look at where it is in your string and how long it is. So by simply counting we know it starts in the 8th character position and its 4 characters long. We use this information with the MID() function

=MID(D10,8,4)

In order to pull the month it get a little more complicated since we need to convert it from an abbreviation to a number. There are several ways of doing this. You could go for a long IF statement which would wind up repeating the pull of the month a 11 times. Instead I decided to use the MATCH() function and built an array of month abbreviations inside it. The MATCH() function will return the number/position of what you are searching for in the provided search list. So as long as we enter the months in chronological order, their position will correspond to their numeric values. As such our formula will look like:

MATCH(LEFT(D10,3),{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0)

The LEFT() function was used to pull the month abbreviation from your string. The 0 at the end tell match to look for an exact match. Important to note, this match method is not case sensitive.

Now to get the day we employ the same principals that we did for pulling the year and we wind up with:

=MID(D10,5,2)

We can now substitute each of the formulas for Year Month and Day into the DATE() function and we will get the date portion of the excel date serial. The formula should look like the following:

=DATE(MID(D10,8,4),MATCH(LEFT(D10,3),{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),MID(D10,5,2))

Now you need to tack on your time portion or figure out the decimal portion. In order to do this I would first recommend trying the TIMEVALUE() function. Since time formats tend to be a lot more standard in format than dates, there is a much higher probability that it will work for you. In order to use TIMEVALUE(), the time portion needs to be ripped from the string. This can easily be done with the RIGHT() function as follows:

=RIGHT(D10,8)

That will give you just the time portion which can then be substituted into the TIMEVALUE() function and looks like:

=TIMEVALUE(RIGHT(D10,8))

If the TIMEVALUE() function does not work for you, then you will need to strip out the hour minutes and seconds and dump their results in to the TIME() function. Do this in the same way you pulled the year and the day for the DATE() function. Just update your character counts. TIME() uses three arguments as follows:

TIME(HOUR,MINUTES,SECONDS)

Now that you have figured out your date portion and your time portion all you need to do is add them together to get all the information into one cell. The resulting formula will look like:

=DATE(MID(D10,8,4),MATCH(LEFT(D10,3),{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),MID(D10,5,2))+TIMEVALUE(RIGHT(D10,8))

Where ever you windup placing that formula, remember to change the formatting on the cell to a custom date. Enter the cell custom format as in the image below.

If you have a list of date times to convert in a column, simply copy your formula and formatted cell down as far as you need to go.

Proof of Concept

Formulas used

For more information on the functions used in the formulas above, follow the links below:

• MATCH
• DATE
• TIMEVALUE
• RIGHT
• MID
• LEFT

{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"}

The { } are used to build a custom list or static array.

Answer 3

If you don't want to write a macro in VBA, a cell formula would work if the format is as you specified:

I am assuming the data is in cell B3

=MID(B3,5,2) & "/" & IF(LEFT(B3,3)="Jan","01",IF(LEFT(B3,3)="Feb","02",IF(LEFT(B3,3)="Mar","03",IF(LEFT(B3,3)="Apr","04",IF(LEFT(B3,3)="May","05",IF(LEFT(B3,3)="Jun","06",IF(LEFT(B3,3)="Jul","07",IF(LEFT(B3,3)="Aug","08",IF(LEFT(B3,3)="Sep","09",IF(LEFT(B3,3)="Oct","10",IF(LEFT(B3,3)="Nov","11","12"))))))))))) & "/" & RIGHT(B3,13)