Can I prevent Excel converting to date format?

[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?

 

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

 

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

 

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

 

[0-0 Wai Wai ^-^]

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

'11/7

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?

 

[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 Wai Wai ^-^]

Thanks so much for your detailed explanation.

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