Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Date formats
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Date formats
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 On Jun 7, 6:02 pm, Melanie wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Date formats
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. -- David Biddulph "Melanie" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Date formats
On Thu, 7 Jun 2007 08:02:00 -0700, Melanie
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Date Formats | Excel Discussion (Misc queries) | |||
Excel Date Formats | Excel Discussion (Misc queries) | |||
Excel Date Formats | Excel Discussion (Misc queries) | |||
Excel Date Formats | Excel Discussion (Misc queries) | |||
Excel date formats | Excel Discussion (Misc queries) |