1

I have a basic Excel 2013 spreadsheet I use to track appliance retirement for an apartment complex. I am trying to get a column to sort by months for a give year. This is currently what I see:

enter image description here

What I would like to have is all the months grouped by year. This will allow me to see what appliances I have for retirement in a given year.

Example of what I am trying to accomplish:

01/02/2012
01/15/2012
02/19/2012
06/22/2012
12/02/2012
01/03/2013
02/17/2013
08/05/2013
02/05/2014
11/15/2014
10/17/2014
etc...

As it stands now it sorts only by month and not by year. I can not for the life of me determine how to accomplish this. A lot of Googling seems to point to a pivot table but this seems like something that should not require that level of complexity to accomplish. The column is formatted for a date value. Can anyone point me in the proper direction to accomplish my goal?

1

What you want to achieve is the default and you shouldn't need to do anything.

The cell contents appear to be TEXT not DATEs. That is the only way they would be sorted in that order.

Assuming that your PC is set to US locale settings, you need to hit F2 then enter on each cell in turn and Excel should recognise it as a date.

To check that Excel really does see it as a date, simply reformat the column to a different date format (e.g. ISO: yyyy-mm-dd). If the on-screen format changes, you are looking at dates, if it doesn't, you have text.

If your PC is not set to US locale, it may be easiest to set it that way temporarily, do the edit, save the workbook and set the locale back again. Your locale is shown in the International Settings in the Control Panel.

  • Weird, retyped a column to right of existing date column. Manually type in same date and formatted for date. Sort works. So apparently original column is NOT dates but text. Any way to show which fields are text? – Mort Feb 16 '14 at 0:45
  • You can add a new column and make it a formula something like =ISTEXT(<cell>), A value of TRUE shows the cell to be text. By the way, using ISDATE may give a true value even for text if the text looks like a date. Alternatively, format the column as GENERAL, it should appear as a set of numbers. – Julian Knight Feb 16 '14 at 15:18
1

If you try to change the format of a date (or any other value) and it doesn't actually change then that's a sure-fire way to know that the data is text formatted. A definitive check is to use ISNUMBER function, e.g. with "date" in G2 use this formula

=ISNUMBER(G2)

If you get TRUE it's a valid date - if you get FALSE it's not

This works because in Excel real dates are simply formatted numbers

For a range of dates you could also use COUNT function which only counts numbers, so if you have fully populated range of dates in G2:G100 then if they are all text formatted the following formula will yield a result of zero

=COUNT(G2:G100)

.....but if they are true dates you'd get a count of those, i.e. 99

If you have "dates" that look like dates in a valid format for your region then it should be possible to convert them all to dates in one step using "Text to columns" functionality

Select date column > Data > Text to columns > Finish

If that doesn't work you may need to actually set the source format like this:

Select date column > Data > Text to columns > Next > Next > at step 3 choose "date" radio button from "column data format" list and then from dropdown select the source date format - in your case MDY > Finish

That latter process should only be required, though, when converting dates from an unrecognised format for your region

0

Make sure that column is formatted as date/time, not as string. Try changing the display to 01 Jan 2012 and back, for instance. If Excel sees the values as dates, the the simple sort should do what you want.

If you need to keep as strings, separate month day year into separate columns (you can hide those columns and display only the concatenation). Then you can do a multi-column sort to do whatever you want.

  • Verified column has all values formatted as dates not string. Changing the format (i.e. 01 Jan 2012) yields same results. Looking for a solution that does not require manually intervention of splitting out months and years if possible. – Mort Feb 15 '14 at 21:04
0

This may sound like a strange fix but so far this is the easiest way I have seen and is caused by some confusion with the time automatically placed within the date cell.

Hit ctrl+f and replace all commas (,) with a blank space ( ) and close the ctrl +f dialogue. Select home on the top left of your screen, select sort and sort by the column "connected on" and order "oldest to newest". Select ok and your data is sorted!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.