Excel help with conversion of text string to date format?

[METALMICKY]

I have a training database that rather unhelpfully exports to Excel the date of training as a string in a single cell " Monday 2 November 2017 ". I'm not interested in the day of the week but just want to convert to date format 02/11/2017 thereby allowing sorting or use in pivot table.

 

[Weststander]

I have a training database that rather unhelpfully exports to Excel the date of training as a string in a single cell " Monday 2 November 2017 ". I'm not interested in the day of the week but just want to convert to date format 02/11/2017 thereby allowing sorting or use in pivot table.

Highlight the cells in question
Format
Custom or Date - both have options for you
And take your pick

 

[HASSOCKS-BHA]

Highlight the cells in question
Format
Custom or Date - both have options for you
And take your pick

This won't work as the text string is too long and unrecognisable as anything that could be converted into a date format (Or any other format come to that). I think your only option will be to amend the export itself but if the data it's reading from says 'Monday 2 November 2017' then there probably isn't much you can do (But I stand to be corrected...)

 

[METALMICKY]

Highlight the cells in question
Format
Custom or Date - both have options for you
And take your pick

If it were only that easy. In the thread title I did mention that date has been imported as a text string.

 

[Billy Seagull]

Try this one Micky
http://chandoo.org/wp/2010/03/23/text-to-date-convertion/

 

[clapham_gull]

I'm a great advocate of the Power Query functionality. You just don't have to deal with rubbish like this any more.

Sent from my LG-K520 using Tapatalk

 

[clapham_gull]

Find the first space.. And remove the day. Then convert to date.

Sent from my LG-K520 using Tapatalk

 

[clapham_gull]

Something like... Subsitute( a1, left(a1, seach(" ", a1)-1), "")

Sent from my LG-K520 using Tapatalk

 

[seagully]

In a new column you can use the MID function to only select the data after the day of week. Unfortunately this formula will vary based on the number of letters in said day of week

For your example in the OP you would use =MID(A1,8,20), which would return 2 November 2017. You can then use the DATEVALUE function to convert this cell to a dd/mm/yy format. You could probably play around with some IF functions to combine the whole thing into one formula

 

[Paul Reids Sock]

Had something similar before and I am sure that I had to convert it to Number and then convert that to date.

It seemed pretty daft at the time but worked from memory

It was in my old job and that was 2 years ago so I may have just got one of the bods to do it for me as they would get frustrated that I couldn't do everything in Excel whilst also knowing the secret cell that launched flight simulator

 

[maffew]

=TEXT(RIGHT(A1,LEN(A1)-FIND(" ",A1))*1,"dd/mm/yyyy")

 

[METALMICKY]

Many thanks Maffew will try that tomorrow. Thst looks incredibly neater than the donkey solution I came up. With a focus in initially getting rid of the day of the week I used Excels Text to Columns feature but did not import the weekday column. I then used the Concantenate to combine the remaining date year month and pasted them as a number. I then used the Datevalue function to turn them into an excel date serial no. Then finally just formatted that as date. Ugly or what!

And all because some idiot didn't to export the date as an excel date rather than a string

 

[KingKev]

=TEXT(RIGHT(A1,LEN(A1)-FIND(" ",A1))*1,"dd/mm/yyyy")

View attachment 87753

Ooh, that's neat. Nice one.

 

[Iggle Piggle]

Of all the threads on NSC an Excel help thread is always somewhere you can learn something.

10 Print 'Excel threads are brilliant'
20 GOTO 10

 

[clapham_gull]

Please power query,available as an add-in on earlier versions too. I barely write a formula.

Sent from my LG-K520 using Tapatalk

 

[maffew]

Many thanks Maffew will try that tomorrow. Thst looks incredibly neater than the donkey solution I came up. With a focus in initially getting rid of the day of the week I used Excels Text to Columns feature but did not import the weekday column. I then used the Concantenate to combine the remaining date year month and pasted them as a number. I then used the Datevalue function to turn them into an excel date serial no. Then finally just formatted that as date. Ugly or what!

And all because some idiot didn't to export the date as an excel date rather than a string

Cool let me know if it works. Only thing is if you pivot it by date it may do it alphabetically so 01 Jan 01 Feb........

Unless its a business requirement to dd/mm/yyyyy (which breaks a lots of countries dates/stops working after the 12 each month) I would use the ISO standard yyyymmdd or yyyy-mm-dd and whether Excel thinks its date or text will order correctly for you either way