Converting Date & Time Stamp to a useable format - need help!

[JNCollee]

I'm trying to convert the following values into an excel friendly date value, time is irrelevant. If possible I'd like to retain the time value, everything I've tried so far though has failed.

[TABLE="width: 263"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Mar 22 2017 02:24 PM[/TD]
[/TR]
[TR]
[TD]Jun 23 2014 12:40 PM[/TD]
[/TR]
[TR]
[TD]Jan 11 2016 05:35 PM[/TD]
[/TR]
[TR]
[TD]Jun 18 2014 01:31 PM[/TD]
[/TR]
[TR]
[TD]Jan 12 2017 03:02 PM[/TD]
[/TR]
[TR]
[TD]Feb 20 2017 11:17 AM[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Here's a user-defined function (UDF) that will do what you want. To use it, first install the function following the instructions below the example of its use which follows.

Excel Workbook
	A	B
1	Mar 22 2017 02:24 PM	3/22/17 2:24 PM
2	Jun 23 2014 12:40 PM	6/23/14 12:40 PM
3	Jan 11 2016 05:35 PM	1/11/16 5:35 PM
4	Jun 18 2014 01:31 PM	6/18/14 1:31 PM
5	Jan 12 2017 03:02 PM	1/12/17 3:02 PM
6	Feb 20 2017 11:17 AM	2/20/17 11:17 AM
Sheet6

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the function just like an Excel worksheet function (see example above).

Function ConvertToDate(S As String)
If Not IsDate(S) Then
    ConvertToDate = CVErr(xlErrNA)
    Exit Function
End If
ConvertToDate = DateValue(Format(S, "mm/dd/yyyy")) + TimeValue(Format(S, "hh:mm"))
End Function

 

[JoeMo]

Forgot to tell you to format the cells the formula is in to return a date and time. If you prefer the full year (yyyy) try this custom format:

[$-409]m/d/yyyy h:mm AM/PM;@

Also, with your data I find that the string S can be used directly as the argument for DateValue and TimeValue

Function ConvertToDate(S As String)
If Not IsDate(S) Then
    ConvertToDate = CVErr(xlErrNA)
    Exit Function
End If
ConvertToDate = DateValue(S) + TimeValue(S)
End Function