# Excel question - sorting by date **URL:** \ **Category:** Factual Questions\ **Created:** [April 28, 2011, 1:41pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028 "2011-04-28T13:41:49Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Panurge](https://avatars.discourse-cdn.com/v4/letter/p/fbc32d/32.png) [@Panurge](https://boards.straightdope.com/u/Panurge)\ **Post date:** [April 28, 2011, 1:41pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/1 "2011-04-28T13:41:49Z")
I have an Excel-spreadsheet that contains data from different sources. The main difference between them is the way the date is set up. Roughly 2/3rds of the entries are dated simply with a year, the remainder with a full date (i.e. 3-23-2008 etc.). When I try to sort the column with dates, I end up with all the “year only”-entries at the top and the rest collected at the bottom of the column. Is there any way to sort the column so the entries will show up in the correct order? - i.e. like this: 5-26-2005 2006 2006 2007 3-10-2008 2010 etc. Thanks. ---
**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\ **Post date:** [April 28, 2011, 1:51pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/2 "2011-04-28T13:51:24Z")
To do that, you need all the cells to be formatted as date fields. Your problem is that fields with something like “2006” in them are probably formatted as general fields or number fields, and if you just reformat them you will get the wrong data. I just tested reformatting a field with “2006” in it, and it became the date June 28, 1905 – what you really want is the date January 1, 2006, displaying with just the year. So what you need to do is: (1) sort on this date field; (2) with the year-only numbers, move them to another column so you can work on their cells; (3) format the cells as a date, with only the year displaying; (4) type in the years into those cells. It’s a bit slow and messy, but I can’t think of an easier way to do it. ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [April 28, 2011, 2:09pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/3 "2011-04-28T14:09:29Z")
I’d sort them the way Giles suggested and then in the column to the right of the original column you want to type: =“1/1/”&A1 I used 1/1, but you can use whatever date you want for the year-only ones. Copy the formula down and then when you have them all filled in copy the right hand column and Paste Special/Values in left column. ---
**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\ **Post date:** [April 28, 2011, 2:18pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/4 "2011-04-28T14:18:56Z")
> [@jonesj2205](#): > > … > =“1/1/”&A1 > … Now that’s something I didn’t know. I frequently have to put strings together in Excel, and I’ve always used the =CONCATENATE function. Using & as a operator saves a lot of typing (as well as remembering how to spell “concatenate”). ---
**Author:** ![Panurge](https://avatars.discourse-cdn.com/v4/letter/p/fbc32d/32.png) [@Panurge](https://boards.straightdope.com/u/Panurge)\ **Post date:** [April 28, 2011, 5:20pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/5 "2011-04-28T17:20:43Z")
> [@jonesj2205](#): > > I’d sort them the way Giles suggested and then in the column to the right of the original column you want to type: > =“1/1/”&A1 > I used 1/1, but you can use whatever date you want for the year-only ones. > Copy the formula down and then when you have them all filled in copy the right hand column and Paste Special/Values in left column. Great suggestions. Thanks, both of you. I imagine that this will simply prefix the date “1/1” to all the figures in those fields? I’ll try it tomorrow. One thing, though: The date actually matters when it’s there, and I don’t really want to add something to the other dates that isn’t true (I don’t think that all of the entries were actually made on Jan 1sts…). Is there any way to add the 1/1 without displaying it? (Sorry for being difficult.) ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [April 28, 2011, 5:24pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/6 "2011-04-28T17:24:47Z")
> [@Panurge](#): > > Great suggestions. Thanks, both of you. I imagine that this will simply prefix the date “1/1” to all the figures in those fields? I’ll try it tomorrow. > One thing, though: The date actually matters when it’s there, and I don’t really want to add something to the other dates that isn’t true (I don’t think that all of the entries were actually made on Jan 1sts…). > > Is there any way to add the 1/1 without displaying it? (Sorry for being difficult.) Just format the cells as yyyy. ---
**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\ **Post date:** [April 28, 2011, 5:31pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/7 "2011-04-28T17:31:54Z")
> [@Panurge](#): > > Is there any way to add the 1/1 without displaying it? (Sorry for being difficult.) You’re not being difficult at all – rather, Excel is a bit difficult. You stop the “1/1” from displaying by: (1) selecting the cells that you want to change; (2) getting the “Format Cells” menu (in Excel 2007, you get it through “Number” on the “Home” menu bar – not very intuitive); (3) select “Date” and click “OK”; (4) get the “Format Cells” menu a second time (!); (5) select “Custom”; (6) click the box labelled “Type”, and change “m/d/yyyy” to “yyyy”; (7) click “OK”. Not very intuitive, but that seems to me the fastest way of doing it. And you still have the problem that typing “2006” into one of those cells gives you a date in 1905 – you need to enter the year 2006 as “1/1/2006”. Now that’s not just difficult: it’s positively nasty. ---
**Author:** ![Panurge](https://avatars.discourse-cdn.com/v4/letter/p/fbc32d/32.png) [@Panurge](https://boards.straightdope.com/u/Panurge)\ **Post date:** [April 28, 2011, 5:42pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/8 "2011-04-28T17:42:42Z")
I’ll give it a go tomorrow. Thanks! also: Why June 28th 1905? (It was the day after the Potemkin uprising in Russia, but other than that I’ve got nothing). ---
**Author:** ![Duke](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@Duke](https://boards.straightdope.com/u/Duke)\ **Post date:** [April 28, 2011, 5:45pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/9 "2011-04-28T17:45:32Z")
> [@Panurge](#): > > I’ll give it a go tomorrow. Thanks! > also: Why June 28th 1905? (It was the day after the Potemkin uprising in Russia, but other than that I’ve got nothing). It’s 2006 days after 1/1/1900. ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [April 28, 2011, 5:45pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/10 "2011-04-28T17:45:34Z")
> [@Panurge](#): > > I’ll give it a go tomorrow. Thanks! > also: Why June 28th 1905? (It was the day after the Potemkin uprising in Russia, but other than that I’ve got nothing). It’s the 2006th day couting from Jan 1, 1900, accounting for Excel’s built in error assuming a leap year in 1900. ---
**Author:** ![Panurge](https://avatars.discourse-cdn.com/v4/letter/p/fbc32d/32.png) [@Panurge](https://boards.straightdope.com/u/Panurge)\ **Post date:** [April 28, 2011, 6:06pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/11 "2011-04-28T18:06:51Z")
Got it. That makes sense. The best thing I came up with by googling was the [execution of Henri Languille](http://www.executedtoday.com/2008/06/28/1905-henri-languille-a-man-of-science/) - a rather odd thing to commemorate in a piece of spreadsheet software. ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [April 28, 2011, 6:09pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/12 "2011-04-28T18:09:11Z")
You could also put the “fake date” into a different column and sort by that. So you’d still have your original data as entered, and a sortable date column. ---
**Author:** ![Panurge](https://avatars.discourse-cdn.com/v4/letter/p/fbc32d/32.png) [@Panurge](https://boards.straightdope.com/u/Panurge)\ **Post date:** [April 29, 2011, 10:18am UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/13 "2011-04-29T10:18:14Z")
Hmm. It doesn’t seem to work. When I do as **Giles** and **jonesj** suggests, I end up with this: ```auto 3-30-2011 3-30-2011 5-1-2011 10-4-2011 10-4-2011 1-1-1981 1-1-1983 1-1-1983 1-1-1983 1-1-1984 ``` All the “fake dates” still gather at the bottom of the list even though I chose “paste special -\> values” and formatted all cells as dates. Any ideas? ---
**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\ **Post date:** [April 29, 2011, 10:42am UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/14 "2011-04-29T10:42:46Z")
That suggests that the 2011 dates are not formatted as dates. Select one of those cells, and see how it is formatted. (The “Number” section of the “Home” toolbar should tell you what it is – General, Number, Date, Text, etc.) ---
**Author:** ![Panurge](https://avatars.discourse-cdn.com/v4/letter/p/fbc32d/32.png) [@Panurge](https://boards.straightdope.com/u/Panurge)\ **Post date:** [April 29, 2011, 10:56am UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/15 "2011-04-29T10:56:55Z")
They are all formatted as “date”. For some reason the “fake dates” are aligned towards the left and the original “real dates” are aligned to the right. I don’t know why or if it matters. ---
**Author:** ![Panurge](https://avatars.discourse-cdn.com/v4/letter/p/fbc32d/32.png) [@Panurge](https://boards.straightdope.com/u/Panurge)\ **Post date:** [April 29, 2011, 11:05am UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/16 "2011-04-29T11:05:48Z")
Hmm it appears that if I _type_ each “fake” date it works. I wonder why it doesn’t work with “paste special -\> value”? Sigh. ---
**Author:** ![steronz](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/steronz/32/11765_2.png) [@steronz](https://boards.straightdope.com/u/steronz)\ **Post date:** [April 29, 2011, 11:09am UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/17 "2011-04-29T11:09:49Z")
I’m not sure if this helps, but when working cutting and pasting in excel, it’s really hard to make excel “forget” a cell’s type. My default practice: Select a column of text/general crap. Open up notepad (windowskey-r, notepad) Paste column of crap into notepad Go back to excel, clear the column contents Format the column as date fields Select all from notepad Paste back into excel When coming from notepad, excel forgets all formatting. ---
**Author:** ![UncleRojelio](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/unclerojelio/32/3160_2.png) [@UncleRojelio](https://boards.straightdope.com/u/UncleRojelio)\ **Post date:** [April 29, 2011, 11:55am UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/18 "2011-04-29T11:55:25Z")
There are ways to do this by using the ‘left’, ‘mid’, and ‘right’ functions to parse out the day, month and year and then reassembling it as a date with the ‘date’ function. ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [April 29, 2011, 12:21pm UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/19 "2011-04-29T12:21:43Z")
you can use a function like =date(A5,1,1) where A5 is the cell where you have the year number like 2011. The difficulty here is that when you sort, you might screw up the formulas - sort + formulas referencing dummy rows = bad. The solution here is to sort via formula, rather than via the sort function. So you get all of the dates in one column (including those created with the above formula) and you make another column with this formula: SMALL($B$4:$B$6,ROW(A1)) with the first two cells in the formula representing the array where the dates are, and the row(a1) just being a convenient way to return a 1 in the first cell’s formula, a 2 in the cell below its formula, etc. Drag down the formula so that you have as many cells as you need to cover all of the dates and you’re done. If you want to sort with the later dates first, use large instead of small in the formula ---
**Author:** ![Panurge](https://avatars.discourse-cdn.com/v4/letter/p/fbc32d/32.png) [@Panurge](https://boards.straightdope.com/u/Panurge)\ **Post date:** [May 2, 2011, 7:53am UTC](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028/20 "2011-05-02T07:53:09Z")
> [@steronz](#): > > I’m not sure if this helps, but when working cutting and pasting in excel, it’s really hard to make excel “forget” a cell’s type. My default practice: > > Select a column of text/general crap. > > Open up notepad (windowskey-r, notepad) > > Paste column of crap into notepad > > Go back to excel, clear the column contents > > Format the column as date fields > > Select all from notepad > > Paste back into excel > When coming from notepad, excel forgets all formatting. This worked like a charm. Thanks. (Can’t believe I didn’t think of this myself - it should be default copy-paste behaviour). [Next page](https://boards.straightdope.com/t/excel-question-sorting-by-date/580028.md?page=2)