CODEWITHSUNDEEP
excel
danstan
danstan

Reputation: 95

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

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?

Upvotes: 0

Views: 5749

Answers (3)

Helper
Helper

Reputation: 1

=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")

Upvotes: -2

Shahar
Shahar

Reputation: 11

Another solution - via change in windows regional settings.

before/after

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.

Windows regional settings

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

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 4

Related Questions