Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Excel 2010 - date field leading space and serial value problems

    Due to a bug in Crystal Reports, date fields export into Excel with a leading space in the cell.

    As on this occasion VBA is not any option, I attempted to remove the leading space with a formula that uses substitute, as follows:

    A1 = leading space + date (format yyyy-mm-dd)
    B1 = substitute (A1," ","")

    This removed the space, but displayed the field as a serial value, instead of the date in A1.

    I then tried:

    B1 = value(substitute(A1," ",""))

    This formatted the field as a date, but left the leading space in the field.

    I would be very grateful for some help with this.

    useful

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    This should do it:
    B1=TEXT(TRIM(A1),"yyyy-mm-dd")


    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    useful (2016-06-06)

  4. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Hi Zeddy

    What can I say ... you are a real genius! I am so grateful that you responded and did it so promptly!

    I only wish I could return the favour!

    Eternally grateful!

    useful

Posting Permissions

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