Converting text field with date to a date formatted field

With your value in cell A1, this formula will isolate the date part:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

by discarding everything after the last space (the time part)

To convert this to a true date, use:

=DATEVALUE(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1))

To do this conversion "in-place" would require a macro.

EDIT#1:

To find any "junk" characters in your data, place a sample date in cell A1. then in B1 enter:

=MID($A$1,ROW(),1)

and copy down. then in C1 enter:

=CODE(B1)

and copy down. You should see:

EDIT#2:

Given your data in A1, this formula:

=--MID(A1,FIND(", ",A1)+2,4)

will return the year (2015). ~~This formula:~~

=LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"January","February","March","April","May",",June","July","August","September","October","November","December"},{1,2,3,4,5,6,7,8,9,10,11,12})

will return the month (7). This formula:

=--MID(A1,FIND(" ",A1)+1,FIND(",",A1)-FIND(" ",A1)-1)

will return the day (14).

Putting this all together:

=DATE(--MID(A1,FIND(", ",A1)+2,4),LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"January","February","March","April","May",",June","July","August","September","October","November","December"},{1,2,3,4,5,6,7,8,9,10,11,12}),--MID(A1,FIND(" ",A1)+1,FIND(",",A1)-FIND(" ",A1)-1))

This formula avoids regional sensitivities associated with DATEVALUE().

EDIT#3:

As you discovered, the month array must be sorted, so use this for the month:

=LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"April","August","December","February","January","July","June","March","May","November","October","September"},{4,8,12,2,1,7,6,3,5,11,10,9})

and this is the "final" formula:

=DATE(--MID(A1,FIND(", ",A1)+2,4),LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"April","August","December","February","January","July","June","March","May","November","October","September"},{4,8,12,2,1,7,6,3,5,11,10,9}),--MID(A1,FIND(" ",A1)+1,FIND(",",A1)-FIND(" ",A1)-1))

Edited to remove a space in the formula (Edits must be 6 char, hence this description)

Gary's Student

Posted 2016-10-21T12:50:14.943

Reputation: 15 540

Awesome, thanks. The first line you've given me is working, but the second is returning an error – David Sigley – 2016-10-21T15:35:46.810

1@DavidSigley You may have non-visible junk characters in the data............I will update the post to show how to find them. – Gary's Student – 2016-10-21T15:49:38.293

1@DavidSigley See my EDIT#1 – Gary's Student – 2016-10-21T15:57:59.093

Thanks! I tried this and got the same results as you. However, it's still causing an error. I added a screenshot in my question – David Sigley – 2016-10-21T16:03:41.877

1@DavidSigley The only thing I can think of is the problem may be related to the date/time system settings – Gary's Student – 2016-10-21T16:16:27.107

2There are a lot of ways to get this problem . To help diagnose, (1) how are you getting the data from the DB into excel. (2) what happens if you go to an empty cell and manually type in the date exactly as it appears. Does excel recognize it as a date? – bvaughn – 2016-10-21T17:09:07.270

1@bvaughn EXCELlent suggestions! – Gary's Student – 2016-10-21T17:19:07.737

1@DavidSigley See my EDIT#2 – Gary's Student – 2016-10-21T22:27:50.970

Wow! This works perfectly, I can't thank you enough for your help. – David Sigley – 2016-10-22T20:04:44.643

1@DavidSigley You are quite welcome.......this is of value to me as well. – Gary's Student – 2016-10-22T20:06:42.780

Oh, I just noticed a funny bug with the month digit. Check out my edit, I have provided a screen grab – David Sigley – 2016-10-22T20:12:02.907

1@DavidSigley You are correct, I'll make a correction in a bit.........it might need a sort within the month array......... – Gary's Student – 2016-10-22T20:17:23.790

1@DavidSigley See my EDIT#3 – Gary's Student – 2016-10-22T20:39:44.857

This appears to be in date/time format already. How are you using it or what is the problem? (there are not 3 underlying data types of Date, Time , & Date/Time. Under the hood there is only Date/Time. You can choose how much you want to use or display...) – bvaughn – 2016-10-21T14:21:38.713

It was taken from my DB and is currently in General format. Any attempt to switch that to DATE, TIME does nothing at the moment. – David Sigley – 2016-10-21T14:38:00.363

The import from DB is messing up the type. If they data is in e7, then in another cell use =DATEVALUE(E7) this should show up as a number. Then convert it to date type. – bvaughn – 2016-10-21T14:44:01.397

Thanks, I read that solution elsewhere but it's not working. It just comes up as #VALUE!. Any thoughts? – David Sigley – 2016-10-21T14:56:37.013

How are you doing the inport from the DB? Also, In a cell type (do not use copy/paste) the exact "July 14, 2015 11:02". Does excel recognized it as a date? – bvaughn – 2016-10-21T15:14:49.517

Converting text field with date to a date formatted field

Answers