Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Date Puzzle

    I was editing some date data that someone else had entered because it was giving me an error with a lookup function. The dates were entered as mm/dd/yy. I entered the LEN function in an adjacent column It returned 9. I checked and found a leading space ahead of some of the dates. I removed the space and len() returned 5. The formula bar shows mm/dd/yyyy and the screen shows mm/dd/yy and the length is considered 5. I am confused. Anyone?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Date Puzzle

    When the cell was first encountered Excel interpreted it as a text value, due to the leading space, so the LEN() function returned 9, representing 6 digits plus 2 slashes, plus the space. When you removed that leading space Excel defaulted to interpreting the value as a date entry.

    The internal representation of a date entry is the number of days since 01/01/1900 (or 1904 - but let's just leave that alone). For example, Jan 15, 2001, is represented as 36,906 - and this five-digit number returns LEN() of five.

Posting Permissions

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