Newbie needs help in sorting out a couple of date columns

[slicksilver79]

Hello all

I'm unable to figure out a way to filter the dates. I would ideally like to sort by month or year or both. The filter does not give me an option to sort it by month or year. Can somebody help? Attachment is attached to this post.

Thanks!

[poetstorm]

Hi there

What I have done, though probably there is a more efficient way, is this.

Create a new column to the right of your date and enter this formula for month:

=month(A1) (Where "A1" is the cell of your first date)

Then make another column and put in:

=year(A1) referring to the same cell where your dates begin.

Now you can sort by the Year column and then by the Month column and lastly by your date column. That should sort it correctly for you. Hope it helps!

[protonLeah]

Your data are not excel dates. They are text strings

[poetstorm]

I see that. I should have checked that before posting. In this case what I would do is copy the dates into another column to the right and seperste out the day, month, and year by using the Text to columns feature and selecting "delimited" by spaces and by commas. This should break it down for you so you can sort, and then delete the extra col;columns when you're done.

Text to Columns can be found on the Data tab in 2007 or by going to the Data menu in 2003 or older.

Here's an example I did using your dates. Hope it helps! The gray columns are my scratch space to get it to sort right. I woudl delete them afterwards. date sort text.xls

[slicksilver79]

Your data are not excel dates. They are text strings

Is there a simpler way to convert the text to dates? Thanks

[slicksilver79]

I see that. I should have checked that before posting. In this case what I would do is copy the dates into another column to the right and seperste out the day, month, and year by using the Text to columns feature and selecting "delimited" by spaces and by commas. This should break it down for you so you can sort, and then delete the extra col;columns when you're done.

Text to Columns can be found on the Data tab in 2007 or by going to the Data menu in 2003 or older.

Here's an example I did using your dates. Hope it helps! The gray columns are my scratch space to get it to sort right. I woudl delete them afterwards. Attachment 144245

Once the data is in three columns how do I put them back in a single text so that I can sort them using the filter later on? Is there another way to do it? Thanks

[poetstorm]

Hi there,

I've found a MUCH simpler way.

Highlight your date column and go to either the Data ribbon in 2007 or the Data menu in older versions.

Click "Text to Columns"

Choose "delimited" and hit next.

When you get to the next screen where it asks you to select if it is comma, space, tab, or semicoln delimited, un-check everything and hit Next.

Now it will bring you to another window where you will see "Column Data Format" choices.

Just select the third option (Date) and then click finish. That's it.

It worked for me using your example sheet.