Can I prevent Excel converting to date format?

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?

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 wrote:
>
> 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

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.

No, I don't know anything about ASP.

et wrote:
>
> 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

"Dave Peterson" <petersod@verizonXSPAM.net> ???
news:431D89E0.DDC629C8@verizonXSPAM.net ???...
> 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?

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 ^-^ wrote:
>
> "Dave Peterson" <petersod@verizonXSPAM.net> ???
> news:431D89E0.DDC629C8@verizonXSPAM.net ???...
> > 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

Thanks so much for your detailed explanation.

"Dave Peterson" <petersod@verizonXSPAM.net> ???
news:431DE86D.7B405545@verizonXSPAM.net ???...
> 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 ^-^ wrote:
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> ???
> > news:431D89E0.DDC629C8@verizonXSPAM.net ???...
> > > 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