Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jan 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula needed to extract a date from a string

    I need a formula that will allow me to extract the first 8 characters of a text string and turn it into a date. Below is an example of the string that I need help with

    2013011606110401M1

    Thanks for your help

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Try the following

    2013011606110401M1 in cell X16, the formula would be:
    =CONCATENATE(MID(X16,5,2),"/",MID(X16,7,2),"/",LEFT(X16,4)) yields 01/16/2013

    Crude, but I am sure there is a much better way to do this.

    HTH,
    Maud
    Last edited by Maudibe; 2013-01-18 at 20:24.

  3. #3
    New Lounger
    Join Date
    Jan 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Maud. I was hoping to find something that would turn it into a date that I could use in formulas

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Sunshine,
    In the above example, if the formula was entered in cell X20 and you wanted to add 1 day to it in the adjacent cell, Y20 = X20 + 1, the result will be the serial number 41291. If you format Y20 as a date, it will then display as 01/17/2013.

    Good Luck,
    Maud

  5. #5
    New Lounger
    Join Date
    Jan 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I found one through Google. It is (using cell A1):

    =DATE(2000+MID(A1,3,2),MID(A1,5,2),MID(A1,7,2))

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Yes, much nicer!

  7. #7
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Why do you need to add 2000!

    =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))

Posting Permissions

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