Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16
  1. #11
    Members
    Join Date
    Dec 2003
    Location
    Umhlanga South Africa
    Posts
    4,700


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

    Name:  Capture.PNG
Views: 108
Size:  13.1 KB

    • North Stand Chat

      advertising
      Join Date: Jul 2003
      Posts: Lots

        


    • #12
      Members
      Join Date
      Jan 2004
      Posts
      1,972


      0 Not allowed!
      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
    • #13
      Members
      Join Date
      Jun 2011
      Location
      Hove (actually)
      Posts
      700


      1 Not allowed!
      Quote Originally Posted by maffew View Post
      This quote is hidden because you are ignoring this member. Show Quote
      =TEXT(RIGHT(A1,LEN(A1)-FIND(" ",A1))*1,"dd/mm/yyyy")

      Name:  Capture.PNG
Views: 108
Size:  13.1 KB
      Ooh, that's neat. Nice one.
      "I'm glad they lost, 'cos if they'd 've won I'd 've been all happy. I hate being happy"
    • #14

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


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

      Sent from my LG-K520 using Tapatalk
    • #16
      Members
      Join Date
      Dec 2003
      Location
      Umhlanga South Africa
      Posts
      4,700


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

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
  •