Sorting by Date (With a Twist)
 

A coworker gave me a list of data. One of the columns of this data was a
date field formated custom in mm/dd/yyyy format. The problem was that
when my coworker sorted the date field it was sorting by the day.

No problem I thought, so I created a new column typed in =year(e2)
which was the cell of the first date. To my suprise the data came back
as 1905. The date in E2 was 5/16/2003. If I did =month(e2), I got
January (in mmmm) format.

What gives?


--
B. Baumgartner
------------------------------------------------------------------------
B. Baumgartner's Profile: http://www.excelforum.com/member.php...o&userid=23107
View this thread: http://www.excelforum.com/showthread...hreadid=393150

If you take your first date (5/26/2003) and simply format it as a number, do
you see 37757?
--
Gary's Student


"B. Baumgartner" wrote:


A coworker gave me a list of data. One of the columns of this data was a
date field formated custom in mm/dd/yyyy format. The problem was that
when my coworker sorted the date field it was sorting by the day.

No problem I thought, so I created a new column typed in =year(e2)
which was the cell of the first date. To my suprise the data came back
as 1905. The date in E2 was 5/16/2003. If I did =month(e2), I got
January (in mmmm) format.

What gives?


--
B. Baumgartner
------------------------------------------------------------------------
B. Baumgartner's Profile: http://www.excelforum.com/member.php...o&userid=23107
View this thread: http://www.excelforum.com/showthread...hreadid=393150

No, actually. No formatting works on the cell. I just created a new
column and entered =floor(e2,1) and then formatted the cell as yyyy and
sorted on the new cell. It worked.

I don't know why excel wouldn't let me change the formatting on that
cell...... It didn't give me an error.


--
B. Baumgartner
------------------------------------------------------------------------
B. Baumgartner's Profile: http://www.excelforum.com/member.php...o&userid=23107
View this thread: http://www.excelforum.com/showthread...hreadid=393150