CELL FORMATTING WILL NOT WORK

[Lindsay Graham]

I'm using Excel 2000 on a Vista desktop and on an XP laptop. I'm finding
that cell formatting will not work any more. For example, if I try to apply
date or custom date formatting to a cell containing '8/6/08', the cell is
not formatted as a date. The only possible thing that I can think of is
that I was trialling OpenOffice and initially I thought that the problem was
limited to Excel files that I had opened in OpenOffice. But I'm now finding
that the problem exists in new files created in Excel that have never been
opened in OpenOffice.

Can anyone help? I've never had this problem before.

 

[Dave Peterson]

Try formatting the cell as a date, but then reenter the date.

Changing the format of a cell won't change the value in that cell. And I'm
guessing that the value in that cell is really text--not really a date.

 

[Gord Dibben]

This problem usually means the "date" is text.

Try Data>Text to Columns>Next>Next>Column Data Format>Date. Pick a format of
DMY or MDY depending upon your short date settings in Windows.


Gord Dibben MS Excel MVP

 

[Shane Devenshire]

Hi Lindsay,

I assume as have others that the dates are really text - so
1. Click an empty cell and choose Copy
2. Select all the date cell and choose Edit, Paste Special, Add
3. Then apply a date formatting of your choosing.

Cheers,
Shane Devenshire
Microsoft Excel MVP
Join http://setiathome.berkeley.edu/ and download a free screensaver and
help search for life beyond earth.

 

[Lindsay Graham]

Thank you, Dave, Gord and Shane. I'm sure you're right and the cells are
formatted as text. However, I've tried each method that you have suggested,
and nothing helps. The date formatting is still not applied.

My preferred custom format is 'd/m/yy_)' but I've tried several standard
date formats as well. I'm really stumped.

Any other ideas out there?

 

[Lindsay Graham]

An additional point that may help someone resolve my problem.

With cells where I have entered a date but have not tried to apply any
format, the format (R click on the cell > Format Cells... > Number tab)
appears as General (ie, no specific number format).

With cells where I have applied a format, it appears as, for example,
'Custom' 'd/m/y_)'. Although this is what would be expected, the date
format is not present -- the cell cannot be used in formulae and will not
sort correctly as a date.

Does this suggest anything to anyone?

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

Thank you, Dave, Gord and Shane. I'm sure you're right and the cells are
formatted as text. However, I've tried each method that you have
suggested, and nothing helps. The date formatting is still not applied.

My preferred custom format is 'd/m/yy_)' but I've tried several standard
date formats as well. I'm really stumped.

Any other ideas out there?

 

[Dave Peterson]

And retyping the date didn't help???

If that helps, then you may have other stuff in those cells--maybe white space
(those HMTL non-breaking spaces???).

An additional point that may help someone resolve my problem.

With cells where I have entered a date but have not tried to apply any
format, the format (R click on the cell > Format Cells... > Number tab)
appears as General (ie, no specific number format).

With cells where I have applied a format, it appears as, for example,
'Custom' 'd/m/y_)'. Although this is what would be expected, the date
format is not present -- the cell cannot be used in formulae and will not
sort correctly as a date.

Does this suggest anything to anyone?

 

[Lindsay Graham]

No, Dave, retyping does not help, I'm afraid.

It's a brand new spreadsheet, and there's definitely nothing else in the
cells.

I remain totally stumped!!

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

And retyping the date didn't help???

If that helps, then you may have other stuff in those cells--maybe white
space
(those HMTL non-breaking spaces???).

 

[Dave Peterson]

I'm out of ideas.

If you start excel in safe mode
close excel
windows start button|Run
type:
excel /safe

and test those suggestions, do they work?

No, Dave, retyping does not help, I'm afraid.

It's a brand new spreadsheet, and there's definitely nothing else in the
cells.

I remain totally stumped!!

 

[Lindsay Graham]

Sorry not to get back earlier, Dave. I've now opened Excel in safe mode as
you suggested and the problem still exists, whether working in a new
spreadsheet or an existing one. In the new spreadsheet, the date format
does not work regardless of whether the cells are formatted before or after
the data are entered.

I opened an existing spreadsheet that has not been used recently. When I
amended a formatted date (and did nothing else), the formatting was lost,
ie, existing formulae using that date returned '#VALUE!'. This happens even
if I amend the cell only by retyping the date that was already there.

As you can imagine, this is seriously bugging me. Does anyone have any
other suggestions?

I've just tried opening some of the same files from a networked Windows XP
and the problem does *not* exist. Sounds to me like it's something to do
with Windows Vista -- are there any known problems with Vista and Excel
2000? I know that Excel 2000 is no longer supported by Microsoft, but I
checked before I started using Excel 2000, and all the advice I got was that
there should be no problems. Help!!??

 

[Dave Peterson]

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.

Sorry not to get back earlier, Dave. I've now opened Excel in safe mode as
you suggested and the problem still exists, whether working in a new
spreadsheet or an existing one. In the new spreadsheet, the date format
does not work regardless of whether the cells are formatted before or after
the data are entered.

I opened an existing spreadsheet that has not been used recently. When I
amended a formatted date (and did nothing else), the formatting was lost,
ie, existing formulae using that date returned '#VALUE!'. This happens even
if I amend the cell only by retyping the date that was already there.

As you can imagine, this is seriously bugging me. Does anyone have any
other suggestions?

I've just tried opening some of the same files from a networked Windows XP
and the problem does *not* exist. Sounds to me like it's something to do
with Windows Vista -- are there any known problems with Vista and Excel
2000? I know that Excel 2000 is no longer supported by Microsoft, but I
checked before I started using Excel 2000, and all the advice I got was that
there should be no problems. Help!!??

 

[Dave Peterson]

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.

 

[Lindsay Graham]

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.

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.

<snip>

 

[Dave Peterson]

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

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.

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.

<snip>

 

[Lindsay Graham]

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.

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

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.

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]

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.

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.

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

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.

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>