Date Menace

[tc]

hi. whenever i try to enter a date in a particular cell for eg.
14/11/07 (i.e. 14 November, 2007) the date that is displayed in the
cell is 7-Nov-14 (i.e. 7 November, 2014) !. i have tried all possible
options in the format cells section and even to the extent of changing
the system regional date settings. Please help!

 

[Niek Otten]

Your Windows Short Date format is set to yy/mm/dd

For Windows XP:

Start>Control Panel>Date, Time, Language and Regional Options>Change the format od Numbers, dates and times>Customize>Date
tab>Short date format


--
Kind regards,

Niek Otten
Microsoft MVP - Excel
--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| hi. whenever i try to enter a date in a particular cell for eg.
| 14/11/07 (i.e. 14 November, 2007) the date that is displayed in the
| cell is 7-Nov-14 (i.e. 7 November, 2014) !. i have tried all possible
| options in the format cells section and even to the extent of changing
| the system regional date settings. Please help!

 

[ovdpost]

hi. whenever i try to enter a date in a particular cell for eg.
14/11/07 (i.e. 14 November, 2007) the date that is displayed in the
cell is 7-Nov-14 (i.e. 7 November, 2014) !. i have tried all possible
options in the format cells section and even to the extent of changing
the system regional date settings. Please help!

I tried to correct the output but i think your problem is on the input
site. A date in excel is represented by a number. 39400 for 14-
nov-2007
and 41950 for 7-nov-2014. Whatever you try to do with the format of
the number 41950, it will never change into 39400.

So, you have to do something to the input format. Problem is that when
your delete your input 14/11/07 the format will still exist. if you
enter it again it will lead again to 41950 (7-nov-2014). To overcome
the problem empty the input cells, go to number format and to custom
and enter: dd/mm/yy (depending on your language. I, for example, have
to put in dd/mm/jj).
If you now enter in this field 14/11/07 the format will be correct.

ps. if you copied the date from an other sheet or source, always use
Past Special - Values. To eliminate the possibility you copy besides
the value also the format of the source.