Page 1 of 2 12 LastLast
Results 1 to 10 of 16
  1. #1
    Members
    Join Date
    Jan 2004
    Posts
    1,954

    Excel help with conversion of text string to date format?


    0 Not allowed!
    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.

    • North Stand Chat

      advertising
      Join Date: Jul 2003
      Posts: Lots

        


    • #2
      Members Weststander's Avatar
      Join Date
      Aug 2011
      Location
      Withdean area
      Posts
      8,969


      0 Not allowed!
      Quote Originally Posted by METALMICKY View Post
      This quote is hidden because you are ignoring this member. Show Quote
      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
    • #3

      0 Not allowed!
      Quote Originally Posted by Weststander View Post
      This quote is hidden because you are ignoring this member. Show Quote
      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...)
    • #4
      Members
      Join Date
      Jan 2004
      Posts
      1,954


      0 Not allowed!
      Quote Originally Posted by Weststander View Post
      This quote is hidden because you are ignoring this member. Show Quote
      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.
    • #5
    • #6
      Progressive Patriot clapham_gull's Avatar
      Join Date
      Aug 2003
      Posts
      16,832


      0 Not allowed!
      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
    • #7
      Progressive Patriot clapham_gull's Avatar
      Join Date
      Aug 2003
      Posts
      16,832


      1 Not allowed!
      Find the first space.. And remove the day. Then convert to date.

      Sent from my LG-K520 using Tapatalk
    • #8
      Progressive Patriot clapham_gull's Avatar
      Join Date
      Aug 2003
      Posts
      16,832


      0 Not allowed!
      Something like... Subsitute( a1, left(a1, seach(" ", a1)-1), "")

      Sent from my LG-K520 using Tapatalk
    • #9
      Cock-knobs!
      Join Date
      Jun 2006
      Location
      Coventry
      Posts
      2,662


      0 Not allowed!
      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
    • #10
      Members Paul Reids Sock's Avatar
      Join Date
      Nov 2004
      Location
      Paul Reids boot
      Posts
      4,077


      0 Not allowed!
      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
      -- he shot, he scored, his mental age is 4...--

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •