Can I prevent Excel converting to date format?

E

et

I was wondering if there was a setting or any way to prevent Excel from
converting certain numbers to dates.

e.g. 11/7 will be converted automatically to 11/07/2005. This appears
as 11-Jul in the cell. This problem also happens with 11-7.

This is very annoying because it would appear that there is actually no
way of having two numbers (which happen to be low enough numbers to be
dates) separated by a forward slash or a hyphen.

Has anyone found a solution to this problem yet?
 
D

Dave Peterson

If you're typing these values into the cell, you can either prefix them with a
leading apostrophe:

'11/7

or preformat the cell as text (format|cells|number tab)
and type your entry.
 
E

et

Thanks for this answer.

Do you happen to know if this is possible if I have a web page
converted to excel by changing the content type to
"application/x-msexcel"

I assume that I would need to change the format of the cell in code if
that's possible. I'm using ASP by the way.
 
D

Dave Peterson

No, I don't know anything about ASP.
Thanks for this answer.

Do you happen to know if this is possible if I have a web page
converted to excel by changing the content type to
"application/x-msexcel"

I assume that I would need to change the format of the cell in code if
that's possible. I'm using ASP by the way.
Click to expand...
 
0

0-0 Wai Wai ^-^

Dave Peterson said:
If you're typing these values into the cell, you can either prefix them with a
leading apostrophe:

'11/7
Click to expand...

Something I wonder:
If you do so, then excel should treat "the date" as **text**.
And you cannot use it to do calculations (eg calculate the date difference
between one and another).

But when I tried it out, it is not true. Calcuation can be made.
How come?
 
D

Dave Peterson

Depends on what calculation you're doing.

If you have a couple of text dates in A1 and B1:
'9/6
'9/9

Excel will do it's best to coerce the value to numbers in a formula like:
=b1-a1

Those arithmetic operators really help.

But other formulas won't do that coersion:

=SUMPRODUCT(--(A1:A10=DATE(2005,9,6)),--(B1:B10="red"))

=======
You can see it with a non-date example.

Put
'1 in A1
'2 in A2

=a1+a2 in A3
=sum(a1,a2) in A4
=sum(a1+0,a2+0) in A5
 
0

0-0 Wai Wai ^-^

Thanks so much for your detailed explanation.

Dave Peterson said:
Depends on what calculation you're doing.

If you have a couple of text dates in A1 and B1:
'9/6
'9/9

Excel will do it's best to coerce the value to numbers in a formula like:
=b1-a1

Those arithmetic operators really help.

But other formulas won't do that coersion:

=SUMPRODUCT(--(A1:A10=DATE(2005,9,6)),--(B1:B10="red"))

=======
You can see it with a non-date example.

Put
'1 in A1
'2 in A2

=a1+a2 in A3
=sum(a1,a2) in A4
=sum(a1+0,a2+0) in A5
Click to expand...
 
Top