Is my Len not working??

[spcalan]

I have a date 5/28/2008

I used Len() and it gives me the value of 5.

Shouldnt it be 9?

 

[Jonmo1]

nope, dates are stored as numbers,

Jan 1 1900 = 1
Jan 2 1900 = 2
etc..
5/28/2008 = 39596 (5 characters)...

Try

Len(Text(A1,"m/dd/yyyy")

Hope this helps...

 

[Richard Schollar]

Hi

Dates are actually held as Integer numbers in Excel and that date is actually the number

39596

whose length is 5. try changing the number format of your date cells to:

0

to see what I mean.

 

[Jonmo1]

More important question is Why are you using Len on a date? What is the ultimate goal, are you trying to extract the month or day from the date??

 

[Aladin Akyurek]

I have a date 5/28/2008

I used Len() and it gives me the value of 5.

Shouldnt it be 9?

5/28/2008 is actually a 5 digit number, hence the result LEN returns.

On the other hand...

=LEN(TEXT(A2,"m/d/yyyy"))

would return what you expected.

 

[Andrew Poulsom]

If it's a serial date Len will return the length of the serial number 39596. You will get 9 if it's text.

 

[spcalan]

Thanks guys.

Work perfect