CODEWITHSUNDEEP
exceldatetimeexcel-formula
iliketolearn
iliketolearn

Reputation: 710

Extract date from the following string format

I cannot seem to get the date from this format in excel: 

 Mon Jun 06 09:33:41 2016

I've tried date() and datevalue() and neither seem to work.

I do not need the time stamp, just month, day and year. I've also tried removing the time and name of day (ie. Mon) and converting Jun 06 2016. This is also not working for me.

Upvotes: 0

Views: 1312

Answers (3)

aspan88
aspan88

Reputation: 617

  1. Select the complete column with the dates, goto Data then click Text to Columns. A window will pop up with Convert text to Columns Wizard as shown below.

enter image description here

  1. Select Fixed width and click next, next and finish. You will get the values separated in columns. Now you have to concatenate values with DateValue to get the desired result.

enter image description here

  1. Enter the formula =DATEVALUE(C1 & " " & B1 & " " & E1) as per different cell values with month, date and year value and change the format to date as it show the date value.

enter image description here

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

The problem is the Mon removing that we can get the correct format:

=DATEVALUE(MID(A1, FIND(" ",A1),LEN(A1)))+ TIMEVALUE(MID(A1, FIND(" ",A1),LEN(A1)))

Then format it as you like.

enter image description here

If you only want the date:

=DATEVALUE(MID(A1, FIND(" ",A1),LEN(A1)))

Upvotes: 3

user4039065
user4039065

Reputation:

Use MID function and REPLACE function to parse the text then the DATEVALUE function to covert the text-that-looks-like-a-date.

=DATEVALUE(REPLACE(MID(A1,5,99),7,9,","))

The result will be numeric (e.g. 42,527). Format the cell as a date.

Upvotes: 4

Related Questions