I think this is closest to what I’d like to see. I’ll try it out, along with a couple of other tricks I’ve gathered off-board.
Excel often tries to outthink you. My company uses alphanumeric account numbers ie: MAR1234 and if I paste a column into excel that contains that value it gets converted to a date starting with March. Same thing if the value starts with DEC, APR, etc. The trick is to format the column to text before pasting.
Having said all that, for the OP question I wonder if it would be practical to use a column for each data element; year, month, day. Those could all be numeric and would avoid sorting dates as alpha characters.
In other words: if the user enters only the year, then the month and day columns are left blank. And if the user enters only the year and month, then the day column is left blank. When you need to sort, sort by all three columns. Voila! Simple, efficient, and no formatting at all to risk tripping up on.
That’s probably the best idea. But as jbolty warned, Excel has a habit of trying to help.
If you enter 1934 in a cell Excel *might *decide that represents a date and change your input to the datenumber for 1/1/1934 (which is 12420) then format the cell to be YYYY so you see “1934” in the box. All without telling you. *If *this happens and you don’t pick up on it you’re going to end up with a mess.
If you’re going to store the dates as y, m, and d parts in separate columns, definitely manually set the format for all your cells to numeric with the correct number of digits and no decimal places before starting to keystroke your bulk of data.
True. I think the classic example is when Excel strips the leading zero off of a zip code. The string “04634” becomes the integer 4634, and sometimes it’s too late to do anything about it.
But I have never seen 1934 or 1978 or 2016 turn into a date. They are simple integers.
[QUOTE=gnoitall]
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.
[/QUOTE]
[QUOTE=Isilder]
When you enter a date and excel decides its a date, its storing it as days since 1/1/1900.
[/QUOTE]
I can work my way around many of Excel’s annoying habits of reformatting things the way it thinks you ought to want them, but I can’t find a way around this limitation on dates.
Firstly, Excel has a year 1.9K problem: you cannot have negative dates, i.e., dates before 1900. This is a problem for various data sets that require earlier dates, e.g., genealogical data.
Secondly, Excel has a year 10K problem: you can’t have dates after 31/12/9999. This isn’t such a big problem for most people, but I can imagine some data that go that far into the future, e.g., predictions of astronomical events.
For the year 1.9K problem, my solution would be to format the cell as text, and enter in the form “1066-10-14” (the date of the Battle of Hastings). Dates from 1 to 999 A.D. could be entered with leading zeroes to make them file correctly. But you still have a year 0K problem: there’s no easy way to enter B.C. dates and make them file correctly.
I use string formating, not integer formating.
(I would get into trouble with dates prior to A.D. 1,000 but that rarely seems to come up in my contacts list!)
Agreed.
All of which is a way to say that Excel is a numerical calculation tool. It is not a database and ought not be used as one. There are end-user accessible 
database tools in the Office suite. Use them and you (any you) will have lots less of this silliness.
Store it as all text entered as CCYYMMDD (20161213 for today, for example). And for ones without a month of day, enter ‘00’. Thus 19340000 if all you have is the year 1934. This has the advantage of making it explicit that this data element was only the year 1934, with no month or day known – not that the person entering data missed entering them. Then all your data is 8 character fields of digits, and can be easily sorted, substringed, etc.
Note that this will sort year-only data elements before those with partial or full dates. If you want them sorted after, use ‘99’ as your placeholder for missing months or days. (But that makes it a bit harder to display them properly.)
Or use jbolty’s suggestion and have separate columns for year, month, day.In data theory, that’s called normalization of the data and is generally a good thing.
Or use LSLGuy’s dsuggestion and use a real database, not Excel, to do database tasks.
I haven’t read the whole thread, but isn’t the simplest solution to use 3 different columns for year, month, day? You can leave blanks wherever you wish, a blank will be come last in the sort. You’ll sort the day column first, the month column second, the year column last, then everything will be in order.
You can easily go back and forth between 3 columns and one column with a true date value using the date() function and the year() month() day() functions.
- Access is not part of Office Home edition. 2) I haven’t used Access in years. 3) All I need is a simple sort command. 4) Spending 10 minutes fiddling is far superior to the hours needed to learn a new database program.
Entering as text solves almost all of my problems. Why complicate things? Quick and dirty wins every time.
I still give thanks to all those who spent their time to give me help.