Cant get dd/mm date format in Excel 2003

[Guest]

I've imported an Excel 200 file into Excel 2003. The date column worked
perfectly as dd/mm on my machine in Excel 2000 but refuses to show that way
in 2003 on another machine. Both are XP, the original "Home edition", the
destination machine on "Professional".

I've gone through Format/cells/number/date/ routine, and
format/cells/number/custom.

I've gone to control panel and Regional Settings . None of these options are
making any difference. Even blank cells in the column will not accept the
date in the typed in format of dd/mm whereas it was never a problem in the
originating machine under Excel 2000.

Any ideas?

Thanks in advance.

 

[Guest]

Just make sure there are real dates in there, not text.

Take a new blank worksheet and enter a date in any cell. Then touch CNTRL-~
several times. You will see real dates toggle back and forth between date
format and number format.

Try this on your problem sheet.

 

[David Biddulph]

It doesn't do that for me with real dates. What is CTRL-~ supposed to do?
It looks to me as if CTRL-~ goes one way and CTRL-# goes the other way, but
they don't toggle for me.

 

[Guest]

On my machines, CNTRL-~ switches to formula display mode and changes the
display of
1/1/2007
to
39083


A second press of CNTRL-~ reverses this.

 

[David Biddulph]

Which version of Excel?

For 2003 the shortcuts are described at
http://office.microsoft.com/en-us/excel/HP052037811033.aspx, but there may
be some confusion on keyboard layout, such as which characters need the
shift key.
http://office.microsoft.com/en-us/excel/HP011116591033.aspx?pid=CH010503701033
shows the list differently, but still no sign of the toggling effect to
which you refer.

Interesting!

 

[Dave Peterson]

Just to be sure...

The shortcut is ctrl-` (ctrl-backquote). On my USA keyboard, it's on the same
key as the tilde (~), but it's toggled without using the shift key.

You can do the same thing through the menus:
tools|options|view tab|check/uncheck formulas

 

[Gord Dibben]

David

CTRL + `(backquote) toggles view formulas on/off.

Since the tilde is on same key G's S used CTRL + ~


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

I've imported an Excel 200 file into Excel 2003. The date column worked
perfectly as dd/mm on my machine in Excel 2000 but refuses to show that way
in 2003 on another machine. Both are XP, the original "Home edition", the
destination machine on "Professional".

I've gone through Format/cells/number/date/ routine, and
format/cells/number/custom.

I've gone to control panel and Regional Settings . None of these options are
making any difference. Even blank cells in the column will not accept the
date in the typed in format of dd/mm whereas it was never a problem in the
originating machine under Excel 2000.

Any ideas?

Thanks in advance.

You really don't give enough information.

How does the date column "show" in Excel 2003?

Are you trying to change how dates are displayed? Or are you trying to change
how dates that you enter from the keyboard are interpreted by Excel?

If the latter
1. Exactly what are you typing in (what numbers and symbols)?
2. Exactly what is displaying in the cell?
3. What is your short date windows regional setting?

If the former,
1. What does the cell display?
2. What is displayed in the formula bar when you select that cell?
3. What is displayed in the cell (and formula bar) when you format the
cell as "General"


--ron

 

[David Biddulph]

Ah, yes. I see the CTRL + ` in the list, and I see what it does.

On my keyboard, the tilde is obtained from SHIFT + #. The page which I
quoted
(http://office.microsoft.com/en-us/excel/HP011116591033.aspx?pid=CH010503701033) shows different functions for CTRL + ~, CTRL + #, and CTRL + `, and each ofthem agrees with what I get on my keyboard.--David Biddulph"Gord Dibben" <gorddibbATshawDOTca> wrote in messagenews:[email protected]...> David>> CTRL + `(backquote) toggles view formulas on/off.>> Since the tilde is on same key G's S used CTRL + ~>> Gord Dibben MS Excel MVP> On Thu, 14 Jun 2007 17:49:25 +0100, "David Biddulph" <groups [at]> biddulph.org.uk> wrote:>>>Which version of Excel?>>>>For 2003 the shortcuts are described at>>http://office.microsoft.com/en-us/excel/HP052037811033.aspx, but there may>>be some confusion on keyboard layout, such as which characters need the>>shift key.>>http://office.microsoft.com/en-us/excel/HP011116591033.aspx?pid=CH010503701033>>shows the list differently, but still no sign of the toggling effect to>>which you refer.>>>>Interesting!>

 

[Guest]

Thanks Ron and others. I've just checked in quickly to see the responses but
unfortunately have someone waiting for me so don't have time to reply. Ron,
I'll give a detailed response in the next day or so. Thanks for giving me
your time.