Excel - LEN of a 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.
 
D

Dave Peterson

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????)
 
R

RagDyer

*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.
 
T

TKT-Tang

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.
 
D

Dave Peterson

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)
 
T

TKT-Tang

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.
 
D

Dave Peterson

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????
 
Joined
Feb 20, 2016
Messages
1
Reaction score
0
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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top