Excel - LEN of a Date

  • Thread starter Thread starter TKT-Tang
  • Start date Start date
T

TKT-Tang

1. Select worksheet cell A1 ; and there, enter a combo of keys : "Ctrl
+ ;". That's showing today's date (2004-05-19) thereof.

2. Enter into cell B1, = LEN(A1) ; it's expected to give 10 ;
neverthelss, the calculated result shows 5.

3. How is it possible to compute the apparent length of a date
(also,there's indeed a plethora of other formats by virtue of MS
doing, you know) ?

4. Regards.
 
If you format that cell as General, you'll see that that date is just a number
(38125 for May 18, 2004 in the 1900 date system).

That number is just a counter from a base date (12/31/1899 for 1900 date
system).

If you want the length of the string:

=len(text(a1,"mm/dd/yyyy"))

might be what you want (but I don't think I've ever checked the length of a date
field--maybe you're just making sure the cell is non-blank????)
 
*XL's* date calculation is *days* after either 1900 or 1904.

Right click in A1, and choose "Format Cells",
Click on "General", then <OK>,
And see what you get!

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

1. Select worksheet cell A1 ; and there, enter a combo of keys : "Ctrl
+ ;". That's showing today's date (2004-05-19) thereof.

2. Enter into cell B1, = LEN(A1) ; it's expected to give 10 ;
neverthelss, the calculated result shows 5.

3. How is it possible to compute the apparent length of a date
(also,there's indeed a plethora of other formats by virtue of MS
doing, you know) ?

4. Regards.
 
Mr. Dave Peterson and Mr. RagDyer,

Thank you for responding to my query.

The essential thing that I would like to test is whether cell A1 has
been formatted as Date or it stays as a pure Number. The difference in
outward appearance between a date and the corresponding number is the
length of the individual items.

Out of the many wonderful things that Excel can manifest, is it
additionally capable of telling the prevalent transfiguration of a
number into a date and vice versa ?

Regards.
 
You can check to see if the cell is formatted as a date.

Look at the worksheet function =Cell():
=cell("format",a1)

In help, you'll see a bunch of date formats and what's returned.

m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"

Then if that cell contains a number, it should be a date:

=IF(AND(ISNUMBER(A1),OR(CELL("Format",A1)={"D1","D2","D3","D4","D5"})),
"It's a date","nope")

(all one cell)
 
Mr. Dave Peterson,

Thank you for responding to my query.

Entering the following formula into a worksheet cell,

B1 = IF(AND(ISNUMBER(A1),OR(CELL("Format",A1)={"D1","D2","D3","D4","D5"})),
"It's a date","nope")

And then, cell A1 is to be formatted "General" or "Custom" (namely,
yyyy-mm-dd ; stemming from the regional settings of the Windows OS).

General formatting turns cell A1 into a number ; Custom formatting
turns cell A1 into a date.

As a result, the given formula is no longer able to tell-them-apart
the distinguishability of an (apparent) date and a number ; It simply
says "nope".

Is there an improvement of the prevalent situation, please ?

Regards.
 
I'm not sure I understand, but if you put a date in a cell and then format as
general, then you're fooling excel too much.

Maybe, maybe:

If you know the values of the dates you want to accept (say jan 1, 2000 to dec
31, 2010), you can just look at the whole numbers between 36526 - 40543.

Maybe you wouldn't be using numbers that high for the other data????
 
Replying to this thread 12 years after the last post only because I figured it out.

Assuming you have a "Short Date" Excel cell A1 displaying something like, "12/31/2010", and you just want the total length of that visual string and not the date numerical value, try this formula:

=(IF(MONTH(A1)<10,1,2))+(IF(DAY(A1)<10,1,2))+6

This presumes three things: you want the date slash separators included, the year is 1900 or later, and the Short Date format reads months as one digit if less than 10 (and the same with the day value).
 
Back
Top