Date string

[alexboe]

Hi,

I'm trying to transform date string formatted like: Jan 1, 2017 6:01:12 AM PST

How do I transform those dates with a formula into a actual date as recognised by excel?

 

[lrobbo314]

I'm sure there will be another, probably better way, but this seems to work.

=DATE(INT(MID(D2,FIND(" ",D2,FIND(" ",D2)+1)+1,4)),INT(MATCH(E2,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)),INT(MID(D2,FIND(" ",D2)+1,FIND(",",D2)-FIND(" ",D2)-1)))

 

[admiral100]

Maybe this ?

=DATE(TRIM(MID(A1,8,5)),MONTH(DATEVALUE(LEFT(A1,3)&" 1")),MID(A1,FIND(",",A1)-2,2))

 

[lrobbo314]

And another option.

=DATEVALUE(LEFT(D20,FIND(" ",D20,FIND(" ",D20,FIND(" ",D20)+1)+1)))

 

[lrobbo314]

And a couple more for fun.

=DATEVALUE(LEFT(D20,FIND(CHAR(1),SUBSTITUTE(D20," ",CHAR(1),3))))

VBA

Function ConvertDate(sDate As String) As Date
Dim AR() As String
AR = Split(sDate, " ")
ConvertDate = DateValue(AR(0) & " " & AR(1) & " " & AR(2))
End Function

 

[admiral100]

And one more

=DATEVALUE(LEFT(D20,12))

 

[Rick Rothstein]

And one more

=DATEVALUE(LEFT(D20,12))

A little shorter...

=0+LEFT(D20,12)

 

[Marcílio_Lobão]

alexboe, Good afternoon.

You really have now a lot of very good options.

I will leave my modest contribution here as well.

If your dates are in a particular column, you can convert them in the same place and permanently using the function TEXT TO COLUMN..

Suppose they are at column A.

Select the cells with the dates.

Data menu -> Text to Column

Fixed with -> Next

Delete the column delimiters and leave only that is after the year 2017. --> Next

The first column of the DATA PREVIEW will be selected, then CLICK the DATE button by selecting MDY.

Click the second column of the DATA PREVIEW and click on the button "DO NOT IMPORT COLUMN (SKIP).

Now just hit FINISH..

All cells are now dates recognized by Excel without the part that you do not want.

It works for a column or for a individual cell too.

I hope it helps.

 

[Rick Rothstein]

If your dates are in a particular column, you can convert them in the same place and permanently using the function TEXT TO COLUMN..

Besides Text To Columns, you can also use Excel's Replace dialog box. Select the column with your date/time values in it, then press CTRL+H to bring up the Replace dialog box, type (or copy/paste) this into the "Find what" field...

????????????PST

Leave the "Replace with" field empty. Press the "Options>>" button and make sure there is no check mark in the "Match entire cell contents" checkbox, then click the "Replace All" button.

 

[Macropod]

Duplicate of: https://www.mrexcel.com/forum/power-bi/1036366-date-string.html

Kindly read Mr Excel's policy in Rule 12 against asking questions about the same topic in multiple threads: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html
Thread closed. You may continue the discussion in your other thread.