Got something to say or just want fewer pesky ads? Join us... 😊

Excel help with conversion of text string to date format?



METALMICKY

Well-known member
Jan 30, 2004
5,935
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

Well-known member
NSC Patreon
Aug 25, 2011
63,390
Withdean area
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

Member
Jun 11, 2012
76
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...)
 








clapham_gull

Legacy Fan
Aug 20, 2003
25,175
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

Legacy Fan
Aug 20, 2003
25,175
Something like... Subsitute( a1, left(a1, seach(" ", a1)-1), "")

Sent from my LG-K520 using Tapatalk
 




seagully

Cock-knobs!
Jun 30, 2006
2,953
Battle
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

Well-known member
Nov 3, 2004
4,458
Paul Reids boot
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

Well-known member
Dec 10, 2003
8,859
Worcester England
=TEXT(RIGHT(A1,LEN(A1)-FIND(" ",A1))*1,"dd/mm/yyyy")

Capture.PNG
 




METALMICKY

Well-known member
Jan 30, 2004
5,935
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
 




Iggle Piggle

Well-known member
Sep 3, 2010
5,281
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

Legacy Fan
Aug 20, 2003
25,175
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

Well-known member
Dec 10, 2003
8,859
Worcester England
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
 



Paying the bills

Latest Discussions

Paying the bills

Paying the bills

Paying the bills

Albion and Premier League latest from Sky Sports


Top
Link Here