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:
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.
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.
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”).
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.)
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.
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).
Got it. That makes sense. The best thing I came up with by googling was the execution of Henri Languille - a rather odd thing to commemorate in a piece of spreadsheet software.
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.)
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.
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.
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