Convert text to date with time

[Luuck234]

Dear All,

I've received a raw data set for which in one column the date including time is available in text (see below), could anybody help me how to convert this text to a date with time?

8/31/2019 11:19:18.659 AM
8/31/2019 11:13:10.659 AM
8/31/2019 9:20:42.612 AM
8/31/2019 9:05:22.638 AM
8/31/2019 8:46:58.645 AM
8/31/2019 8:11:45.627 AM
8/31/2019 7:56:25.618 AM
8/31/2019 7:50:17.615 AM
8/31/2019 7:37:54.597 AM
8/31/2019 7:07:14.590 AM
8/31/2019 7:01:06.603 AM
8/26/2019 7:46:29.967 AM
8/26/2019 7:25:01.956 AM

Many thanks!

[Sam Capricci]

I cannot replicate your data, it converts to date with time for me. BUT, I saw this trick and hopefully it'll work for you, highlight the entire column, go to the data tab, click on text to columns then when it opens, leave it at delimited (if it isn't clicked, click delimited), then hit finish.
see if that does the trick for you.

[Luuck234]

Thank you for your quick reply, I've tried this but unfortunately it does not work.

[Luuck234]

I've now also attached an excel file in which the format is as I've explained, maybe this helps finding the solution

[joeu2004]

The solution depends on your regional short-date configuration; that is, your default date format.


1. If it is m/d/yyyy, simply enter formulas of the form =--C2 into E2:E259.

The double-negate ("--") causes Excel to interpret the text as date/time and return the internal numeric value.


2. If your default date format is d/m/yyyy (anything except m/d/yyyy), it would be prudent to first enter formulas of the form =ISTEXT(C2) into E2:E259 to ensure that everything is indeed text.

In your example, it is. But I worry that your actual data includes some misinterpreted numeric date/time values. For example, 8/7/2019 might be misinterpreted as July 8.

Assuming everything is indeed text, enter formulas of the following form into E2:E259 [1]:

Please Login or Register  to view this content.

In either case, format E2:E259 as Custom m/dd/yyyy h:mm:ss.000 AM/PM. Then copy E2:E259 and paste-value into wherever you want (C2:C259 or E2:E259). You might need to adjust the format again.

-----

Some caveats....

The Formula Bar does not display fractional seconds. But the cell value is indeed accurate to the millisecond.

Moreover, in the Formula Bar (and if you format a cell value as m/d/yyyy h:mm:ss AM/PM without fractional seconds), Excel rounds fractional seconds for display purposes only.

So, for example, the cell value of the converted date/time 8/31/2019 3:29:31.797 PM in E2 is exactly that. But it is displayed as 8/31/2019 3:29:32 PM. Moreover, SECOND(E2) returns 32 instead of 31.

In fact, the date/time 8/30/2019 10:59:59.678 PM in E260 is displayed as 8/30/2019 11:00:00 PM; and HOUR(E260) returns 11, MINUTE(E260) returns 0, and SECOND(E260) returns 0.

Moreover, the date/time 8/30/2019 11:59:59.678 PM in E261 is displayed as 8/30/2019 0:00:00 AM instead of 8/31/2019, due to an Excel formatting defect (in Excel 2010, at least).

Again, despite how they might appear, the actual cell values are exactly as you entered (or converted) them.


-----
[1] I apologize for the complexity of the formula in #2. I am "sure" that I provided a simpler solution in the past. But I just cannot think of it, off-hand. Perhaps someone else will.

[Luuck234]

Dear joeu2004, thank you very much for your elaborate answer! my apologies for the late reply. I've tried your advise but got an error. I've attached a screenshot of the error. It gives the error in the last part of the formula, in the + TIMEVALUE(..) part. Do you have an idea how to fix this error?

[JeteMc]

I don't know why you should get an error as the three functions, TIMEVALUE, MID and FIND are all listed as being supported in the 2003 version of Excel.
(Side note -- Since the file attached to post #1 is .xlsx it seems that you have access to a more recent version)
That said this modification to Joe's formula may work for you (tested using file attached to post #1):

Please Login or Register  to view this content.

Let us know if you have any questions.