1. Select a cell to be formatted I specific date format.
2. Right click and choose "Format Cells...".
3. In the "Number" tab select "Custom" and type the format you want. For example: dd.mm.yyyy
4. Click "OK" button.
In my case, I occasionally import dates into Excel
from a *.csv file that I have created with my favorite text editor,
but I was never able to get the dates in Excel
to change to my preferred "yyyy.MM.dd" format.
(which is great for chronological sorting using 'A to Z' sorting.)
Today, for the first time, I imported a *.csv file generated
by a Windows command prompt, so I thought that the dates,
like "11/21/2014", as an example, would be easy enough to change.
I was wrong.
Over the years, I have occasionally stopped,
and spent hours upon hours,
and visited web-pages upon web-pages,
trying repeatedly and unsuccessfully
every recommendation I had found
to get dates to change to the format I wanted.
Today I found the answer.
---
If you are having trouble changing an existing set of dates,
you may first need to simply specify to Excel
that your existing set of cells with dates,
are in fact dates.
The following method can change any number of cells
in a single column, if they are are adjacent to each other,
or you can change an entire column.
If, however, you have multiple columns to change,
or if the dates are in a single column but are not consecutive,
you will have to repeat this process for each set of dates
that are in different columns,
or that are within the same column but separated
by cells that you do not want to have specified as date cells.
This is the process from that page,
though with slightly 'modified for clarity' wording.
1. Select (highlight) a set of consecutive cells in one column,
(or an entire column,) for which you want to change the date format.
("Consecutive' means the cells are 'adjacent',
or 'all connected' to each other)
2. Click the Excel tab "Data",
and in its group 'Data Tools',
click "Text to Columns".
3. At the window 'Convert Text to Columns Wizard - Step 1 of 3':
Leave the radio-button on the default choice "Delimited",
and click "Next >".
4. Uncheck all five of the 'Delimiters',
and click "Next >".
5. In the box 'Column data format',
you need to pick the format that defines the data as it now exists.
In our case, you want to click the radio-button "Date:".
6. To the right of 'Date:' is a text-field box,
and to the right of that is a downward pointing arrow
which you should click,
and choose one of six combinations of the letters
'Y', 'M', and "D' that represents the date data
as it now exists in your worksheet.
7. Click the button "Finish".
Don't panic if your cells display "#######'. Just make the column wider.
If you had previously tried without success to format the cells using 'Format Cells',
you will find that the cells you have just used for the above process are now formatted according to the last choice you made when you tried using 'Format Cells'.
---
Date and time formats
You will probably have to experiment a bit.
Date:
I have seen the international standard date notation listed as
YYYY-MM-DD
though
yyyy-MM-dd
might work just as well.
Keep in mind that a lower-case 'm' is generally reserved
for use as 'minutes'.
'yyyy' assures you that you get a four-digit year.
'MM' assures you that the first nine months get a leading zero.
'dd' assures you that the first nine days get a leading zero.
Time:
I have seen posted that the international standard notation
for the time of day is
hh:mm:ss
but that exact format might require an additional symbol to distinguish
AM from PM.
And so, I much prefer to always use the twenty-four-hour format
HH:mm:ss
Using 'hh' or 'HH'
instead of using 'h' or 'H'
assures you that the first nine hours get a leading zero;
and I would guess that using 'mm', and 'ss',
which I always do,
is a good way of making sure that the first nine-minutes/seconds
get a leading zero.
has helped 1801 users this month
plz help me anybody and plz send advice to ***@***