How can you sort dates based on year rather than by month??

[BRIZZLE1]

Trying to sort dates based on year, rather than by month or day. All dates are in one column and I'm trying to avoid separating into multiple columns as I am constantly adding new information to the document. That said, I am really hoping there is an easier way to sort them by year, without needing to separate the dates into different columns.

Sorting by A or Z only sorts based on month, so need to figure out how to sort rows based on one of the columns date, but paying attention to the year as the sorting factor.

Thank you so much in advance,

Bryan

 

[barry houdini]

Hello Bryan, welcome to MrExcel

If you have "true" dates in a column then a sort would put them in chronological order......thereby effectively sorting by year anyway. If yours sort by month then that suggests that they aren't real dates, what do these dates look like, presumably they are text-formatted?

 

[BRIZZLE1]

thanks for reply and welcome.

how can I figure out if they are "true" dates? if they aren't, can I turn them into "true" dates? is there a way to attach documents on this thread?

 

[BrooksTech]

Sorting Unformatted Dates by Year (Using Customized-Formats)

Dont worry about what the format is. Just format the column as date format. I did one today where both date AND time were together as TEXT, so I just selected "Custom" and entered:

mm-dd-yyyy hh:mm

Since you dont like messing much with raw data, I recommend the better way by keeping one sheet for RAW data, then referencing it via formulas in a Primary Sheet. Then when you dump new data into the RAW one, the Primary auto-populates nice and clean looking.

 

[BRIZZLE1]

Re: Sorting Unformatted Dates by Year (Using Customized-Formats)

didn't work. still doing by month. can I attach doc to this to see where I'm going wrong?

 

[barry houdini]

You can't attach anything here, Bryan.

If you can describe what the dates look like that will be a start.....

Check whether they are "true dates" by using ISNUMBER function. If your first date is in A2 try this formula in B2

=ISNUMBER(A2)

FALSE means it's not a real date. If that's the case then dates will sort as if the entries are text....so if the month comes first then that will be why the dates sort as they do.

You might be able to convert to real dates using Text to columns functionality.

Select column of dates

Data > text to columns > Next > Next > under "column data format" select "date" and then the "source format"...I'm guessing MDY > Finish

Now try sorting again

 

[BrooksTech]

Sorting Unformatted Dates and Stripping Away Hidden Encoding

Something may be jacked-up in your data. I just formatted a TEXT COLUMN and hand-entered four different dates, and it auto-sorted them by year-first REGARDLESS! The months were in different sequence to the next row, but all occured in appropriate year, month and day sequence! A prompt popped up and asked me if I wanted to treat numbers as numbers, but I never told it that they were dates, it just somehow figured it out automatically! Try it.

I suggest you copy the whole sheet and do a Paste-Special, selecting "Values," into a fresh-clean sheet. This will strip-away any encoding that may be present. There has been a time when I had to paste to an entirely new FILE because some embedded programming was messing up my data. Lets hope that doesnt become necessary, but surely theres something wrong hiding in what you have based on your answer.

 

[BRIZZLE1]

Thanks so much for the additional feedback and my apologies about my delay.
ok, so here's is what I learned:
1. using the =ISNUMBER(A2) method= the data is NOT sorted dates. it's stores as text.
2. reformatting the data as dates, does not fix it.
3. repasting the document into a new spreadsheet as "values only" also does not do the trick.

Because of what I'm trying to do and the "constantly adding new information," text to columns" function would actually be more work than doing it manually (with eyeballs)

I just realized that the data does NOT have commas between the dates, could this be why??? if yes, is there an easy way to add or insert commas into the dates automatically? example: January 31 2011 (vs. should be January 31, 2011)

any ideas or solution??

thanks SO much once again. First time ever being on a blog like this, and so far it's outstanding!!!

Bryan

 

[MikeWx]

If all of your dates are 2010 or more recent, you can use the Replace part of Find and Replace. In Find What, type " 201" (without the quotes). In Replace With, type ", 201" (without the quotes). After you press OK, the values are converted to Excel dates.

 

[Nalani]

If your data is similar to below:
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 117px"><COL style="WIDTH: 28px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">January 31 2002</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">FALSE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">March 1 2002</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">FALSE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">December 25 2002</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">FALSE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">January 31 2001</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">FALSE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">March 1 2001</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">FALSE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">December 25 2001</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">FALSE</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Formula in C1 is =ISNUMBER(C1) copied down. This shows me that they are not True numbers

As Barry posted above, Highlight your range of data and

Data > text to columns > Next > Next > under "column data format" select "date" and then the "source format"...I'm guessing MDY > Finish

This will also put your comma after the Day.

Then Sort.

View after the above steps:
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 120px"><COL style="WIDTH: 28px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">January 31, 2001</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TRUE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">March 1, 2001</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TRUE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">December 25, 2001</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TRUE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">January 31, 2002</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TRUE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">March 1, 2002</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TRUE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">December 25, 2002</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TRUE</TD></TR></TBODY></TABLE>

December 25, 2001 & 2002 is actually on one line. It just posted funny here.