I want to input a a series of dates in Excel 2010 and have them be sortable by year. One problem is that for some I just have the year, for some the month and year and for a few the month, day, and year.
I’d prefer to sort them with the year first, as 1934-06-05, 1934-06, and 1934. That’s not an option. Excel doesn’t seem to offer any options in Format Cells in either Date or Custom with the date first.
I tried the 14-Mar-01 option. Sometimes that gives me 1934-06-05 as I want, when I enter 1934-06-05. But sometimes it appears as 5-Jun-34 and sometimes as other variations.
Is there a way to make Excel do what I want? If not, what’s the closest that I can rely on not to randomly display the numbers?
If the data is being stored as values (as opposed to text), then the format they’re displayed in won’t affect the sort order. 6-5-1935 is always going to sort before 2016-06-05, because they’ll be sorted by value, not by how they’re displayed. Unless you convert all the values to text.
Also, I’m pretty sure the date “1935” is going to be stored as January 1, 1935, so that will sort ahead of any other date in 1935, even if you just display the year.
Things to be aware of:
Excel stores the date based on a system where every date is a sequential whole number. Today, 12/12/2016, is 42716. Yesterday was 42715, etc. So if you enter the number 1935 and then format it as a date, you’ll get 4/18/1905.
When you sort dates, they’ll sort based on the date value, like I said above.
If you don’t need to use the dates in any calculations, you could just store them as text (put a single quote in front of your entry, like '2016-06-05. The single quote will not be visible in the cell, but will mark the entry as text, and you can sort accordingly.
Formatting cells doesn’t change the underlying data itself. It only affects the way that it is displayed. Additionally, date datatypes are specific and cannot be partial values like you describe. They must contain a year, month and day even if the latter are just defaulted to January 1st.
There are a number of ways of approaching this problem but formatting probably isn’t one of the better ones. It may be best to treat the values as plain text and strip out the year using a formula and other relevant components and sort by that.
As long as the year is the first 4 digits of a cell, it is easy to isolate using a formula like =LEFT(A1,4). I don’t know what all of your data looks like but you can generally build formulas to figure out what the data means as long as it is consistent. If it isn’t consistent, that is more of a data problem than an Excel issue.
No. One of the strengths of Excel is that it can handle inconsistent formatting. If it recognizes the date correctly, it will sort it correctly. If there’s some reason to retain the different formats from your sources, you can do so. If you (for yourself, not Excel) want year first, follow the instructions in post #2.
If the date is stored as text with the year listed first, that will work. And likewise, if the year is at the end of the text string you can use =RIGHT(A1,4). You’ll end up with the date as a 4-character string.
If A1 contains a date (regardless of how it’s formatted), you can isolate the year with the function =YEAR(A1). This will give you the year portion of the date, in integer form.
I usually prefer using date values instead of text strings, because it’s easier to format and perform calculations like finding the number of days between A1 and B1. But converting it all to text might be simpler, depending the need, especially if you want to have some values represent a specific date, some a month, and some a year.
Because a date is never just a year. It’s always a day of a month of a year. At least, that’s what Excel expects. A date is actually stored as a number–an integer which is the number of days after its “epoch” date January 1st, 1900.
If you just type in a year, that’s just an integer too. And Excel treats it in all date-related calculations as an internal-format date integer, so computes “this number of days after the epoch” as the date to be displayed and calculated.
So the cheap but probably unsatisfactory answer is to give Excel what it demands: dummy day and month to go along with year. If you just mean “1973”, put in “1/1/1973” and give the baby the candy it wants.
ETA: Excel actually doesn’t demand a day of the month, according to the experiments I just did (Excel 2016). put in “1/1973” and it becomes the date “January 1, 1973” (however you format that) and works in a year-extraction function.
Enter the four-digit year. Then, on the Home tab, in the Alignment section of the ribbon, use the drop-down menu to select the Number format. That will let you store the year as a number instead of a date.
Enter the year with a single quote at the beginning: '1973. This will store it as a text string instead of a number.
Yes, that’s true. For that he’ll either have to go to all text, or store them all as dates and use 1/1/YYYY to store year-only values.
Actually, OP, if you have a year-only date you could enter it as 12/31/YYYY and then use Boyo Jim’s instructions in post 2 to use a custom format of YYYY. That will display just the year, and when you sort, it will be sorted after other dates within that year. If you want month and date you can use the custom format MM/YYYY, or MMM/YYYY if you want the abbreviated month name (i.e. “Feb 1927”).
Not sure if I’m reading you right but if you want the value 1934-06-05 to be sequenced before the value 1934 you are going to need to manipulate the value.
By default entering 1934 is numeric, 1934-06 is text (keyed as 06-1934 it’s a date)and 1934-06-05 is a date (numeric value 12575).
As pointed out above you need the values to be sorted to be in a consistent type, the format is not of importance.
The “natural” ascending sequence of the values above is:
1934 (number)
Jun-34 (date)
12575 (date)
1934-06 (text)
You can get the sequence you want if the underlying sorted values are numeric, text or dates. By date is both easiest and most consistent logically.
My only other suggestion is don’t change your source values. If you manipulate the values have the results in another column and sort on that.
Plus, if I’m understanding correctly, the most critical factor is that you want “1934” and “Jan 1 1934” and “Jan 1934” to be sorted distinct from each other.
This is absolutely not going to happen in a date field, because (as gnoitall wrote) 1934 is not a date. The only solution will involve a text field, where the data is left justified, in optional YYYY or YYYYMM or YYYYMMDD formats. (Remember to right-justify the MM and DD!) My guess is that this field will be calculated from other column(s), and the nature of the other column(s) will be based on whatever is your current and convenient way of entering the data.
In this scenario, “Jan 1934” could be the integer 19,340,100. Gotta remember to include the last two zeroes to keep it distinct from Jan 1 1934. Also, if you omit the trailing zeroes, and use integer format, then ALL of the year-only dates will be sorted first, and ALL of the year-month dates next, with ALL the year-month-day dates coming last.
You could write a function that grabs out the year from various text or numeric inputs,
and creates a year from it…
Then create a new column which just has year as a text field or as a numeric, which ever one you want.
When you enter a date and excel decides its a date, its storing it as days since 1/1/1900.
For sure, formatting is not a solution … setting “date” format on text types won’t work.
You didn’t notice that when excel recognised it as a date, it formats the display as a date, but stores the date as the integer since epoch.
I think this is the best option; in order to meet the OP’s request that
The year is at the beginning of the cell
Full dates are sorted before just date/month
Date/month entries sort before just a year:
Enter everything in date format as “YYYY-MM-DD.” If you only have the month, use the last day of the month for DD and format that date as “YYYY-MM” using custom formatting. (The day will still be stored as the end of the month, so this date will sort after other days in the month).
If you only have the year, enter the date as “YYYY-12-31” and use custom formatting to display the cell as “YYYY”. It will still store the month and day, so it will sort after all other dates of the year.
The only downside is you have to select your custom format (either YYYY or YYYY-MM) for each cell where you want to hide the day or day/month values, for which you enter a default end-of-month or end-of-year date.