Excel Date formats

[Guest]

I have a spreadsheet with a column that lists dates. To format the column I
have gone to Edit, Clear, All Formats. Then with the column still selected
choose Format Cells, Date and choose the dd/.mm/yyyy option. However, when
I begin entering dates in some are being let justified, some are right
justified. Some dates I enter as 6/5/2003 will appear as 06/05/2003 while
other dates appear without the leading 0.

When I try to filter on the date to find everything that is entered in 2003
for example it does not include the 06/05/2003 in the filtered list.

Any help would be greatly appreciated!
Melanie

 

[vezerid]

This is because your computer has English (US) settings or something
similar, and expects dates to be entered in mm/dd rather than dd/mm
format. It is one thing to format somethig that is already a date into
a format of choice, it is another to try to enter to Excel a date in a
format not recognized.

Go to Control Panel, Regional Settings and choose your country's
settings.

HTH
Kostis Vezerides

 

[David Biddulph]

The ones which aren't being affected by the formatting are presumably text,
not dates. You may have spaces or other non-printing characters, or you may
be trying to enter dates which don't match your Windows regional settings.
[20/12/07 won't work if your regional settings are loooking for MDY.]

As for your filtering, what criteria are you using for the filter? If you
are looking for text strings including "2003", it won't pick up real dates.

 

[Ron Rosenfeld]

I have a spreadsheet with a column that lists dates. To format the column I
have gone to Edit, Clear, All Formats. Then with the column still selected
choose Format Cells, Date and choose the dd/.mm/yyyy option. However, when
I begin entering dates in some are being let justified, some are right
justified. Some dates I enter as 6/5/2003 will appear as 06/05/2003 while
other dates appear without the leading 0.

When I try to filter on the date to find everything that is entered in 2003
for example it does not include the 06/05/2003 in the filtered list.

Any help would be greatly appreciated!
Melanie

I believe you may be under the misconception that the cell format determines
how Excel interprets what you type in as a date. It does not. The cell format
merely determines how the contents of the cell are displayed.

To determine how to parse date input, Excel looks at the "short date" setting
under Control Panel/Regional Settings. This is most likely the source of your
confusion.
--ron