The only guess that I have is that you have an event macro running that's
modifying the cell.

When you say you typed in that date and got this back: 39692.67, I would have
guessed that it had to be an addin. Chip and Jan Karel's instructions will help
you isolate that addin.

Typing a date means that you're entering a whole number--no fractions. I don't
have another guess why you're getting that.

Lindsay Graham wrote:

I really appreciate all your efforts, Dave, but we do not seem to be getting
very far <g.

I opened Excel in safe mode, opened a new workbook, formatted the first
column as General and entered the dates as set out in the earlier post. The
results were exactly the same, and again the entry '10 Jun 2008' returned a
number which, when formatted as a date, was nearly 3 months later than the
date entered. ISNUMBER() returned the same results as before. Once again,
Ctrl-; displayed today's date (in dd/mm/yyyy format) but ISNUMBER() returned
FALSE.

Then I closed Excel, reopened it in safe mode, opened a new workbook, did
the VBE thing as you specified (what does this do?), entered dates as above
and, once again, got exactly the same results.

I presume the Chip Pearson and Jan Karel Pieterse sites are only useful if
the second result above had worked as it should have. But I'll have a look
at them when I get back later today.

Any other ideas? -- or have you thrown up your hands and given up? <vbg

--
Lindsay Graham
Canberra, Australia
---------------------------------------------
Please reply only to the list/newsgroup so that all may benefit.

"Dave Peterson" wrote in message
...
I'm gonna ask you to open excel in safe mode once more.
Then format column A as General.
Then do the same test that you did before, but add that ctrl-; version,
too.

What do you see from that test.

It sure sounds like you have an event macro that's modifying the data as
you
enter it.

One more test -- in fact, you can try this first.

Open that troublesome workbook (or any workbook for you!).
Hit alt-f11 to get to the VBE (where macros live)
hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = false

Then back to excel (alt-f11 will take you there) and type your dates
again. If
everything works ok, then you have a butt-in-ski (technical term!) event
macro
that's "helping" you.

If that's the problem, you're going to have to do some detective work to
find
out what it is.

Chip Pearson has some notes on how to diagnose startup errors:
http://www.cpearson.com/excel/StartupErrors.htm

And Jan Karel Pieterse has more notes:
http://www.jkp-ads.com/Articles/StartupProblems.asp





Lindsay Graham wrote:

Thanks again, Dave. I think I've tried all your suggestions, and I'll
try
to explain what happened.

My Windows date format is d/m/yy. So 10 June 2008 appears as 10/6/08.

When I type the first column below into an Excel spreadsheet, the second
column is the result of ISNUMBER():
June 10, 2008 FALSE
10 June 2008 FALSE
10/6/2008 FALSE
10/6/08 FALSE
10 Jun 2008 TRUE

This is where it gets even more mystifying -- when I type the last entry
it
appears as 39692.67. If I then reformat that cell with any Date format
or a
Custom format that is a date [eg, d/m/yy_), which is my preferred
format],
it is formatted as instructed, but it appears as 1/9/08 or 1 September
2008
etc NOT 10 June 2008!!

Ctrl-; returns today's date in dd/m/yyyy format (or d/mm/yyyy, I can't be
sure which it is) which is not my specified Windows format d/m/yy.
ISNUMBER() returns FALSE, and any date formatting applied to that cell
has
no effect.

I'm now convinced that there is a setting somewhere that is causing this
weird behaviour, but I have no idea where to start looking. Does any of
this suggest anything to you?

--
Lindsay Graham
Canberra, Australia
---------------------------------------------
Please reply only to the list/newsgroup so that all may benefit.

"Dave Peterson" wrote in message
...
Ps.

Try selecting an empty cell and hitting:
ctrl-;
(control semicolon)

It should put the current date into the cell. You can look at the
formula
bar
to see the mdy, dmy, ... order that you need to use.

The thing you see in the formula bar does not have to match what you
see
in the
cell, though.

Dave Peterson wrote:

If you try to use text in a calculation, it can cause that #value!
error.
But
excel is very forgiving. If it thinks it looks like a number (or a
date), it'll
coerce the value to a number in its calculations.

But your entry doesn't look close to a number/date for excel to do
this.

Try typing in an unambiguous date in A1 of a test worksheet.
January 1, 2008
(spelled out)

Then put:
=isnumber(a1)

If you see false, then excel is not seeing that as a number/date.

What are you typing into that cell?

Is it something like:
01/28/2008

If your windows date setting is set for dmy order, then this is not a
date.
Excel won't be able to help. You have to enter your dates in the same
order as
that windows setting.

28/01/2008
would work ok.

The way the date is entered and the way you format the cell don't have
to
match,
though.

<snip


--

Dave Peterson

--

Dave Peterson