Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I have an excel file which is created by a fund raising application probable delphi. However the expiration date field exports dates sometimes as a text format dd/mm/yyyy and sometimes as a date format yyyy-mm-dd. I tried selecting the text values and applying the datevalue function but that did not work. So how can I convert a text value of 31/03/2009 to a date value 2009-03-30.

    num Titre date expiration
    2287 31/03/2009
    2409 Vice-président 2007-01-01
    4 Directrice des communications 31/03/2009
    6 Secrétaire générale 2002-01-05
    1774 31/03/2010

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say your "dates" are in column C, starting in C2.

    In row 2 in the first available column, enter the formula

    =IF(ISERROR(FIND("-",C2)),DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)),C2 )

    and fill down as far as needed.


  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Use " Text to Columns " way :

    Select all column C "dates" >> Format Cell >> Number >> Date >> and choose : 2001-03-14 >> OK

    >> Data >> Text to Columns >> Delimited >> Next >> Next >> choose : Date >> select : YMD form the dropdown list >> Finish

    Regards
    Bosco

Posting Permissions

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