How to change the date format in Excel 2007 [Solved/Closed]

Question

Ask a question Report

How to change the date format in Excel 2007 [Solved/Closed]

PANKAJ - Updated on Nov 12, 2018 at 12:42 AM - Latest reply: Praveen - Aug 26, 2017 at 10:01 PM

Excel Ask a question Report

Hello,

i want to know that howcan I change the dateformat in
excel 2007 worksheet if I want it like :-

monday,1dec.2008

plz. answer me soon.

regards
pankaj

Ask a question

anonymos · Accepted Answer · 2009-01-21T07:13:11-0500

Best answer

Report Respond to anonymos

anonymos Jan 21, 2009 at 07:13 AM

39

Thank you

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.

Say "Thank you" 39

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2750 users this month

View all 21 comments

Report

Christopher_James_Francis_Rodger

Posts: 1
Registration date: Thursday February 4, 2016
Last seen: February 4, 2016

- Feb 4, 2016 at 03:11 AM

.
The problem:

"Format Cells" does not format the cells' dates.

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.

The following link was my salvation.

---

The solution

Cannot change the date format in Excel 2007 [Solved/Closed]
http://ccm.net/forum/affich-350882-cannot-change-the-date-format-in-excel-2007

---

The solution in more detail

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.

Google would say,
https://www.google.com/search?q=time+and+date+notations

---

"All the best; intended."

- Chris

Report

Micuzzo - Feb 5, 2016 at 12:17 AM

Thank you Chris!!!

Report

anonymos - Feb 11, 2016 at 11:28 AM

Thank you VERY, VERY much for your answer! So helpful!!

Report

Dhinesh - May 11, 2016 at 12:27 AM

I have to chage date from 42464 this format like this 21-Apr-16
plz help me anybody and plz send advice to ***@***

Report

cooper - Apr 3, 2017 at 06:00 AM

Thank you so so so much

Report

Praveen - Aug 26, 2017 at 10:01 PM

Thank u

Audio (7,848)

Hardware (45,957)

Internet/Social Networks (32,008)

Linux/Unix (4,101)

MacOS (1,772)

Messaging (32,884)

Mobile (27,576)

Network (12,099)

Office Software (15,818)

Programming (2,435)

Software (20,710)

Video (138)

Video Games (8,174)

Viruses/Security (4,528)

Webmastering (860)

Windows (30,623)

Bugs and Suggestions (967)

Members' Cafe (1,612)

Opinions About Reviews (99)

Opinions About the News (1,151)

Ranking Software (6)

How to change the date format in Excel 2007 [Solved/Closed]

1 reply

Your reply