Sorting by Date and Time

I transformed some data to an excel file. I tried to sort those data by date and time, however it didn’t work. I also tried to change the format of the date to like “mar 2 98� or “03/02/98�, but the appearance is always “3/2/98�. It just refuse to change the format. It would be appreciated if anybody could help with this.

Hi

when you right mouse click on one of the cells, go into format / cells &
choose the number tab, click on general ... what does it say under the word
sample on the right hand side?

Cheers
JulieD

"plumstone" wrote in message
...
I transformed some data to an excel file. I tried to sort those data by
date and time, however it didn't work. I also tried to change the format of
the date to like "mar 2 98" or "03/02/98", but the appearance is always
"3/2/98". It just refuse to change the format. It would be appreciated if
anybody could help with this.

It is just the same of the natural appearance of the cell: "5/7/04" in the sample area.
If I type some new data in that cell like:"05/07/04", then in the sample area it shows 38110. And the date is going to return to normal and I can sort them by date and time or change to any format I want.
But I do not want to retype all of those date and time and I want to sort them in date, while it refuseds to be either changed in format nor sorted by date and time.

"JulieD" wrote:

Hi

when you right mouse click on one of the cells, go into format / cells &
choose the number tab, click on general ... what does it say under the word
sample on the right hand side?

Cheers
JulieD

"plumstone" wrote in message
...
I transformed some data to an excel file. I tried to sort those data by
date and time, however it didn't work. I also tried to change the format of
the date to like "mar 2 98" or "03/02/98", but the appearance is always
"3/2/98". It just refuse to change the format. It would be appreciated if
anybody could help with this.

"5/7/04"

"JulieD" wrote:

Hi

when you right mouse click on one of the cells, go into format / cells &
choose the number tab, click on general ... what does it say under the word
sample on the right hand side?

Cheers
JulieD

"plumstone" wrote in message
...
I transformed some data to an excel file. I tried to sort those data by
date and time, however it didn't work. I also tried to change the format of
the date to like "mar 2 98" or "03/02/98", but the appearance is always
"3/2/98". It just refuse to change the format. It would be appreciated if
anybody could help with this.

Hi

bad news - you need to change the format of all the cells
good news - you don't need to retype them

to sort or do anything with dates Excel needs to recognise them as dates (ie
like th 38110 one) ... somehow or other your dates have been entered as
"text" and this is why you can't do anything with them.

to change it, type the number 1 into a blank cell (that's not formatted as
text - ie it should go right aligned after you press enter)
copy it
select all the "date" cells that you need to change the format on (if they
are all consecutive, click on the first, hold down your shift key and click
on the last cell in the range - if they're not consecutive, click on the
first, hold down your ctrl key and then click on each cell you want to
include keeping the ctrl key down) ... once you have all your cells let the
shift or ctrl key go
choose EDIT / PASTE SPECIAL from the menu
choose MULTIPLY
click on the Ok button
-the cells should all go like 38110
now keep the cells selected, choose FORMAT / CELL from the menu
choose
Date (under the number tab) and select whatever format you want
Click OK
and it should now be "fixed"

let us know how you get on

Cheers
JulieD


"plumstone" wrote in message
...
It is just the same of the natural appearance of the cell: "5/7/04" in the
sample area.
If I type some new data in that cell like:"05/07/04", then in the sample
area it shows 38110. And the date is going to return to normal and I can
sort them by date and time or change to any format I want.
But I do not want to retype all of those date and time and I want to sort
them in date, while it refuseds to be either changed in format nor sorted by
date and time.

"JulieD" wrote:

Hi

when you right mouse click on one of the cells, go into format / cells &
choose the number tab, click on general ... what does it say under the
word
sample on the right hand side?

Cheers
JulieD

"plumstone" wrote in message
...
I transformed some data to an excel file. I tried to sort those data
by
date and time, however it didn't work. I also tried to change the format
of
the date to like "mar 2 98" or "03/02/98", but the appearance is always
"3/2/98". It just refuse to change the format. It would be appreciated
if
anybody could help with this.

Perhaps try Data Text to columns on the column with the "dates"?

Suppose these are all in col A, A1 down

Select column A

Click Data Text to columns

Click Next Next to get to step 3 of the wizard

Under Column data format:
Check the button for "Date"
(the drop arrow should show "DMY"
- this is what your data looks to be like)

Click Finish

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik atyahoodotcom
----
"plumstone" wrote in message
...
It is just the same of the natural appearance of the cell: "5/7/04" in the
sample area.
If I type some new data in that cell like:"05/07/04", then in the sample
area it shows 38110. And the date is going to return to normal and I can
sort them by date and time or change to any format I want.
But I do not want to retype all of those date and time and I want to sort
them in date, while it refuseds to be either changed in format nor sorted by
date and time.

"JulieD" wrote:

Hi

when you right mouse click on one of the cells, go into format / cells &
choose the number tab, click on general ... what does it say under the
word
sample on the right hand side?

Cheers
JulieD

"plumstone" wrote in message
...
I transformed some data to an excel file. I tried to sort those data
by
date and time, however it didn't work. I also tried to change the format
of
the date to like "mar 2 98" or "03/02/98", but the appearance is always
"3/2/98". It just refuse to change the format. It would be appreciated
if
anybody could help with this.

much better solution than mine!

"Max" wrote in message
...
Perhaps try Data Text to columns on the column with the "dates"?

Suppose these are all in col A, A1 down

Select column A

Click Data Text to columns

Click Next Next to get to step 3 of the wizard

Under Column data format:
Check the button for "Date"
(the drop arrow should show "DMY"
- this is what your data looks to be like)

Click Finish

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik atyahoodotcom
----
"plumstone" wrote in message
...
It is just the same of the natural appearance of the cell: "5/7/04" in
the
sample area.
If I type some new data in that cell like:"05/07/04", then in the sample
area it shows 38110. And the date is going to return to normal and I can
sort them by date and time or change to any format I want.
But I do not want to retype all of those date and time and I want to
sort
them in date, while it refuseds to be either changed in format nor sorted
by
date and time.

"JulieD" wrote:

Hi

when you right mouse click on one of the cells, go into format / cells
&
choose the number tab, click on general ... what does it say under the
word
sample on the right hand side?

Cheers
JulieD

"plumstone" wrote in message
...
I transformed some data to an excel file. I tried to sort those data
by
date and time, however it didn't work. I also tried to change the
format
of
the date to like "mar 2 98" or "03/02/98", but the appearance is
always
"3/2/98". It just refuse to change the format. It would be appreciated
if
anybody could help with this.

There are several ways to convert text numbers/dates to real numbers/dates:

http://www.contextures.com/xlDataEntry03.html

plumstone wrote:
It is just the same of the natural appearance of the cell: "5/7/04" in the sample area.
If I type some new data in that cell like:"05/07/04", then in the sample area it shows 38110. And the date is going to return to normal and I can sort them by date and time or change to any format I want.
But I do not want to retype all of those date and time and I want to sort them in date, while it refuseds to be either changed in format nor sorted by date and time.

"JulieD" wrote:


Hi

when you right mouse click on one of the cells, go into format / cells &
choose the number tab, click on general ... what does it say under the word
sample on the right hand side?

Cheers
JulieD

"plumstone" wrote in message
...

I transformed some data to an excel file. I tried to sort those data by

date and time, however it didn't work. I also tried to change the format of
the date to like "mar 2 98" or "03/02/98", but the appearance is always
"3/2/98". It just refuse to change the format. It would be appreciated if
anybody could help with this.






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi JulieD,
Thanks a lot. It is going to save me a lot of my time in the next few weeks.
I just signed in tonight and this is my first question. And I am so glad to get your prompt help.
Plumstone

"JulieD" wrote:

Hi

bad news - you need to change the format of all the cells
good news - you don't need to retype them

to sort or do anything with dates Excel needs to recognise them as dates (ie
like th 38110 one) ... somehow or other your dates have been entered as
"text" and this is why you can't do anything with them.

to change it, type the number 1 into a blank cell (that's not formatted as
text - ie it should go right aligned after you press enter)
copy it
select all the "date" cells that you need to change the format on (if they
are all consecutive, click on the first, hold down your shift key and click
on the last cell in the range - if they're not consecutive, click on the
first, hold down your ctrl key and then click on each cell you want to
include keeping the ctrl key down) ... once you have all your cells let the
shift or ctrl key go
choose EDIT / PASTE SPECIAL from the menu
choose MULTIPLY
click on the Ok button
-the cells should all go like 38110
now keep the cells selected, choose FORMAT / CELL from the menu
choose
Date (under the number tab) and select whatever format you want
Click OK
and it should now be "fixed"

let us know how you get on

Cheers
JulieD


"plumstone" wrote in message
...
It is just the same of the natural appearance of the cell: "5/7/04" in the
sample area.
If I type some new data in that cell like:"05/07/04", then in the sample
area it shows 38110. And the date is going to return to normal and I can
sort them by date and time or change to any format I want.
But I do not want to retype all of those date and time and I want to sort
them in date, while it refuseds to be either changed in format nor sorted by
date and time.

"JulieD" wrote:

Hi

when you right mouse click on one of the cells, go into format / cells &
choose the number tab, click on general ... what does it say under the
word
sample on the right hand side?

Cheers
JulieD

"plumstone" wrote in message
...
I transformed some data to an excel file. I tried to sort those data
by
date and time, however it didn't work. I also tried to change the format
of
the date to like "mar 2 98" or "03/02/98", but the appearance is always
"3/2/98". It just refuse to change the format. It would be appreciated
if
anybody could help with this.

um .. not really ..
imho, any solution that works is good g
... but thanks for the feedback, JulieD !

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik atyahoodotcom
----
"JulieD" wrote in message
...
much better solution than mine!