Date is not displaying correctly

[Guest]

I have typed in a date 12/01/07 and it displays correctly until I move from
the cell the it changes to 1/12/07 or something like 37896. Nothing I try
seeems to fix this. I have tried changing the date format, it is currently
set to show as 12/01/07. I have tried deleting the cell and starting again. I
have tried removing the whole column and starting again.I dont know what has
happened but since I tried typing it in another empty cell it showed as a
wierd number in the empty cell so I just deleted that and tried again in the
correct cell and now all I get is the date changing to 1/12/07. Also as soon
as I type a date in this cell it makes my formula appear with an error! I am
using Excel version 2007. THis is driving me crazy, can anyone help?

 

[Peo Sjoblom]

What kind of formula error do you get?


look under tools>autocorrect option and see if you have anything that would
replace it there.
Check your regional settings in the control panel to make sure you have the
date format you want
Look under tools>options>transition and uncheck anything there

 

[Guest]

The error message is Formula Omits Adjacent Cells..

 

[Guest]

Could not find anything under autocorect that would help. Regional settings
are ok. I cant find anything about transition. Excel 2007 does not have a
"tools" option. It has an Office button with Excel options, but I looked
under all the options there and could not find transitions. I am very new to
Excel.

 

[Peo Sjoblom]

Transition is still there under office button>excel options and advanced
The strange thing is that 12/01/2007 is US date format and 01/12/2007 is UK
date format of the same day. That's why I thought it might be regional
settings
If you get 37896 that is the same as 10/02/03, so it seems that you have the
1904 date system first and then it was changed to the 1900 date system.
They should also be in excel options, I am not on a PC with 2007 at the
moment but I know they are still there


--
Regards,

Peo Sjoblom

 

[Guest]

Ok. I have found the transition button and it was already unchecked. I went
back to regions and had another look, I played around with different
countries and the date changed correctly except for English- Australian
(which is where I am) and I tried UK to see what happened and it also
reverses the date when I move from the cell. I had a look and the 1904 box is
not checked. I checked and saved , unchecked and saved again but no
difference. Funny thing is when I type in 12/1/07 into cell it appears in
formula bar as 12/1/2007, then I move from the cell and it goes to 1/12/07.
Any more ideas?

 

[Ron Rosenfeld]

Ok. I have found the transition button and it was already unchecked. I went
back to regions and had another look, I played around with different
countries and the date changed correctly except for English- Australian
(which is where I am) and I tried UK to see what happened and it also
reverses the date when I move from the cell. I had a look and the 1904 box is
not checked. I checked and saved , unchecked and saved again but no
difference. Funny thing is when I type in 12/1/07 into cell it appears in
formula bar as 12/1/2007, then I move from the cell and it goes to 1/12/07.
Any more ideas?

What is the format of the cell itself?

In Excel 2003 that is obtained from the Format/Cells/Number option.

For example, in Excel 2003, if my Windows Regional Settings are US, and I
format the Excel cell with a UK format, I will get exactly what you describe
above.
--ron

 

[Guest]

In the format cells, I have chosen date, with English(Australia).
I dont know how to find out what region my windows is set to. Any suggestions?

 

[Guest]

I found the settings and you were right it was set to US English and as soon
as I changed it to Australia the problem with the date was solved. Thank you
so much!

 

[Ron Rosenfeld]

I found the settings and you were right it was set to US English and as soon
as I changed it to Australia the problem with the date was solved. Thank you
so much!

Ah Ha! Glad to help.

The Excel date parser interprets what you type according to the Windows
Regional Settings, which you found under Control Panel.

The cell formatting merely determines how the value in the cell is displayed --
but has no effect on how input to that cell is interpreted.
--ron