Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Convert Text to Date (Excel2002)

    It's worked before...what is wrong now?
    The value function is not giving the answer...please see the attached!
    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert Text to Date (Excel2002)

    The middle part should consist of 2 characters, not of 1, so use MID(A1,3,2) instead of MID(A1,3,1).
    Don't forget to format the cell containing the formula as a date.
    You can also use DATEVALUE instead of VALUE.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert Text to Date (Excel2002)

    I did fix the MID to extract two numbers and I also formatted the cells to date...but #Value is still showing...even if I use DATEVALUE??
    See updated attachment!
    Is it working for you?
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert Text to Date (Excel2002)

    Yes, it works for me, probably because the values in column A are in ddmmyy format, and my system uses the same order DMY. You apparently use the US date format MDY, so you must change the formula to

    =DATEVALUE(MID(A1,3,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert Text to Date (Excel2002)

    Tx...so that was the problem...I was on the US regional settings.
    Fixed...Cheers
    Regards,
    Rudi

Posting Permissions

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