Date glitch? First 12 days of month format differently than the r

[Guest]

I've run into a very odd date format problem in excel 2003: When I type in a
date as MM/DD/YY and the date is less than 12 (e.g. 04/11/07), excel
automatically changes the format to MM/DD/YYYY, after which I can play with
the date format to get it to look how I want (e.g. 11-Apr-07).

However, if the date is greater than 12 (e.g. 04/13/07) excel leaves the
cell as text, and its format cannot be changed to a date, even by going to
"format cell" and selecting "date." Has anyone else noticed this? Try it
with this example and you'll see what I mean!

It may seem like a minor glitch, but big problems arise when downloading
banking data (i.e. the first 12 days of the month have different date formats
than the rest, and can therefore not be sorted by date), and when trying to
export (when exporting to .qif and uploading to Quicken the two date formats
import differently).

Help!

 

[Niek Otten]

Check the Date format in your Windows settings (Control Panel, Date-Time etc)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I've run into a very odd date format problem in excel 2003: When I type in a
| date as MM/DD/YY and the date is less than 12 (e.g. 04/11/07), excel
| automatically changes the format to MM/DD/YYYY, after which I can play with
| the date format to get it to look how I want (e.g. 11-Apr-07).
|
| However, if the date is greater than 12 (e.g. 04/13/07) excel leaves the
| cell as text, and its format cannot be changed to a date, even by going to
| "format cell" and selecting "date." Has anyone else noticed this? Try it
| with this example and you'll see what I mean!
|
| It may seem like a minor glitch, but big problems arise when downloading
| banking data (i.e. the first 12 days of the month have different date formats
| than the rest, and can therefore not be sorted by date), and when trying to
| export (when exporting to .qif and uploading to Quicken the two date formats
| import differently).
|
| Help!

 

[Guest]

Irrespective of the format used in the cells, you will notice that the date
in the formula bar (That area where you enter and edit formulas) should
always be in the short date format of the operating system. Even though Excel
can handle a variety of formats for entering dates, it gets confused with
some formats, particularly reversing dd/mm, so as a rule of thumb, if you
enter dates as per the operating system date format and leave Excel to the
formatting pertaining to the cells to alter the format then you should not
have any problems.

Regards,

OssieMac

 

[Guest]

check control panel-regional and language options
it may be trying to go dd/mm/yy instead