Convert text date/time to a real date time in excel

Question

My data is extracted from an application and it has a text that looks like a date/time in excel. How do I actually convert "3/24/2016 11:22:07 PM" (in text) to a real date/time conversion? I've tried formatting the cells but it doesn't work.

Answer 1

For a date conversion:

=DATEVALUE(TEXT(A1,"MM/DD/YYYY"))

For a time conversion:

=TIMEVALUE(TEXT(A1,"HH:MM:SS"))

For datetime conversion:

=DATEVALUE(TEXT(A1,"MM/DD/YYYY"))+TIMEVALUE(TEXT(A1,"HH:MM:SS"))

Where A1 has the data you wish to convert.

By the way, then you may wish to format the cell to a date/time or whatever.

Hope that helps.

Answer 2

1) try using the DATEVALUE function and see if that works for you.

2) A more reliable way, since datevalue does not always work is to strip the text out manually and insert it into and excel date value. You are going to want to use a combination of the following functions:

• DATE
• TIME
• IF
• FIND
• MID
• LEFT
• RIGHT
• LEN

Now in my opinion the easiest way to do this is to work with multiple helper columns to build out all the steps. One column per step. When you get your final answer, you can substitute or copy paste your formulas from the helper columns into the final formula until you are left with one variable. The reason I say this is that the final formula referring to only 1 variable gets rather lengthy/ugly and very hard to trouble shoot if you make a typo, forget a bracket or something goes wrong. When I did this approach I used a totally of 14 columns (includes final formula). When I packed it all up into 1 formula it resulted in this:

DATE(RIGHT(LEFT(A3,FIND(" ",A3)-1),4),LEFT(LEFT(A3,FIND(" ",A3)-1),FIND("/",LEFT(A3,FIND(" ",A3)-1))-1),MID(LEFT(A3,FIND(" ",A3)-1),FIND("/",LEFT(A3,FIND(" ",A3)-1))+1,FIND("/",LEFT(A3,FIND(" ",A3)-1),FIND("/",LEFT(A3,FIND(" ",A3)-1))+1)-FIND("/",LEFT(A3,FIND(" ",A3)-1))-1))+TIME(LEFT(RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))-1)+IF(AND(LEFT(RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))-1)<12,RIGHT(RIGHT(A3,LEN(A3)-FIND(" ",A3)),2)="AM"),0,12),MID(RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))+1,FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))+1)-FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))-1),MID(RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))+1)+1,2))

Note it is set up using cell A3 as the one with the time as text that needs formatting.

3) You should also be able to use excel's text to column function located on the DATA ribbon about half way across.

4) And of course there will be a way to code it through VBA as an option as well.

Answer 3

=DATEVALUE(A1)+TIMEVALUE(A1) seems to work as well, since each function only returns the value corresponding to what it recognizes in the string. That is, DATEVALUE() ignores the time component, while TIMEVALUE() ignores the date component.

Answer 4

The accepted answer for full date-time conversion is overly complicated. Excel's VALUE() function will achieve the desired conversion without needing to call DATEVALUE() and TIMEVALUE() separately:

For a "texty" date in cell A1 (such as "3/24/2016 11:22:07 PM" as supplied in the question):

=VALUE(A1)

will return the equivalent Excel date-time value (roughly 42453.97).

Note that VALUE() relies upon the regional settings in use. The above conversion will work if US date format is in use on the machine but not with UK date format. This is also true of TEXT(), DATEVALUE() and TIMEVALUE(), so the accepted answer also won't work in the scenario where the text date doesn't match the current regional settings.

If your date is in an unsupported format you will either need to change your operating system's regional settings or parse the date yourself from the original text.

In short: simply use VALUE(), not a convoluted combination of TEXT(), DATEVALUE() and TIMEVALUE().