Excel Date Puzzle

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote