convert MMM DD, YYYY HH:MM:SS am in excel

Question

I have a spreadsheet which contains the loading times for my website. I want to find the peak hour for my website.

However the dates are stored in the following format:

Jun 02, 2014 01:13:08 am
MMM DD, YYYY HH:MM:SS am / pm

How can I convert this in to date that excel will understand?

Do you have an English language version of Excel? If so, there may be a non-printing character in the string -- probably a NBSP (ASCII 160). Try doing a Find/Replace to remove those characters. — Ron Rosenfeld, Jun 02 '14 at 09:09
As a test, can you tell me what you get as a result of the formula: =A1+0 when A1 contains a slight modification to your string example: 02 Jun, 2014 01:13:08 am (be sure to format the cell as text before entering this string)? — XOR LX, Jun 02 '14 at 09:30
See http://stackoverflow.com/questions/23587037/excel-vba-extract-the-correct-dates-from-badly-formatted-dates — MP24, Jun 02 '14 at 09:47

score 3 · Accepted Answer · answered Jun 02 '14 at 11:41

3

To convert your "pseudo-dates" into real dates, select them and run:

Sub ConvertDates()
    Dim r As Range
    For Each r In Selection
        r.Value = CDate(r.Text)
    Next r
End Sub

answered Jun 02 '14 at 11:41

Gary's Student

Errors if there is data in-between that does not match the format (Like "N/A"), but works well... – Gert van den Berg Jan 04 '17 at 08:24
Not working on OS X, depending on regional settings.... See [this](https://stackoverflow.com/questions/41738609/vba-cdate-not-working-on-mac-excel-201114-7-1) – Gert van den Berg Mar 03 '17 at 10:39

score 0 · Answer 2 · edited Oct 18 '21 at 13:24

0

Another solution - via change in windows regional settings.

Start with .CVS file with date in MMM DD", "YYYY HH:MM:SS AM/PM

In windows 10, Open:

Control Panel > Clock and Region > Region

In long date, enter MMM dd,yyyy > OK.

Reboot, then open the excel file, the text is recognized as date.

edited Oct 18 '21 at 13:24

Toni

answered Oct 16 '21 at 22:51

Shahar

score -2 · Answer 3 · edited Jan 19 '23 at 16:14

-2

=TEXT(DATEVALUE(MID(A1,4,FIND(",",A1)-4)&"/"&MONTH(LEFT(A1,3)&1)&"/"&MID(A1,FIND(",",A1)+2,4))+TIMEVALUE(RIGHT(A1,8)),"dd/mm/yyyy hh:mm")

edited Jan 19 '23 at 16:14

jmoerdyk

answered Jan 19 '23 at 13:21

Helper

While this code may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply – jmoerdyk Jan 19 '23 at 16:14

3 Answers3