# Question about formatting dates in Excel 2010 **URL:** \ **Category:** Factual Questions\ **Created:** [December 12, 2016, 4:33pm UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525 "2016-12-12T16:33:53Z")\ **Posts on this page:** 11\ **Page:** 2
**Author:** ![Exapno\_Mapcase](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/exapno_mapcase/32/1051_2.png) [@Exapno\_Mapcase](https://boards.straightdope.com/u/Exapno_Mapcase)\ **Post date:** [December 13, 2016, 4:39pm UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525/21 "2016-12-13T16:39:59Z")
> [@Skammer](#): > > 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. > > You’ll end up with (for example): > > 1935-01-24 > 1935-02-12 > 1935-02 > 1935-03-11 > 1935-06 > 1935 > > 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. 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. ---
**Author:** ![jbolty](https://avatars.discourse-cdn.com/v4/letter/j/7c8e57/32.png) [@jbolty](https://boards.straightdope.com/u/jbolty)\ **Post date:** [December 13, 2016, 9:51pm UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525/22 "2016-12-13T21:51:22Z")
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. ---
**Author:** ![Keeve](https://avatars.discourse-cdn.com/v4/letter/k/f07891/32.png) [@Keeve](https://boards.straightdope.com/u/Keeve)\ **Post date:** [December 14, 2016, 2:04am UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525/23 "2016-12-14T02:04:49Z")
> [@jbolty](#): > > 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. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [December 14, 2016, 2:41am UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525/24 "2016-12-14T02:41:30Z")
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. ---
**Author:** ![Keeve](https://avatars.discourse-cdn.com/v4/letter/k/f07891/32.png) [@Keeve](https://boards.straightdope.com/u/Keeve)\ **Post date:** [December 14, 2016, 4:39am UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525/25 "2016-12-14T04:39:07Z")
> [@LSLGuy](#): > > But as **jbolty** warned, Excel has a habit of trying to help. 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. ---
**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:** [December 14, 2016, 5:14am UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525/26 "2016-12-14T05:14:36Z")
[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. ---
**Author:** ![Trinopus](https://avatars.discourse-cdn.com/v4/letter/t/2bfe46/32.png) [@Trinopus](https://boards.straightdope.com/u/Trinopus)\ **Post date:** [December 14, 2016, 6:20am UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525/27 "2016-12-14T06:20:58Z")
> [@Keeve](#): > > Sorta kinda. > > 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. 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!) ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [December 14, 2016, 1:59pm UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525/28 "2016-12-14T13:59:20Z")
> [@Giles](#): > > 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. > … 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. ---
**Author:** ![Tim\_T-Bonham.net](https://avatars.discourse-cdn.com/v4/letter/t/46a35a/32.png) [@Tim\_T-Bonham.net](https://boards.straightdope.com/u/Tim_T-Bonham.net)\ **Post date:** [December 15, 2016, 5:40am UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525/29 "2016-12-15T05:40:07Z")
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. ---
**Author:** ![Riemann](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/riemann/32/3133_2.png) [@Riemann](https://boards.straightdope.com/u/Riemann)\ **Post date:** [December 15, 2016, 7:23am UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525/30 "2016-12-15T07:23:06Z")
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. ---
**Author:** ![Exapno\_Mapcase](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/exapno_mapcase/32/1051_2.png) [@Exapno\_Mapcase](https://boards.straightdope.com/u/Exapno_Mapcase)\ **Post date:** [December 15, 2016, 3:57pm UTC](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525/31 "2016-12-15T15:57:08Z")
> [@t-bonham@scc.net](#): > > Or use LSLGuy’s dsuggestion and use a real database, not Excel, to do database tasks. 1. 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. [Previous page](https://boards.straightdope.com/t/question-about-formatting-dates-in-excel-2010/774525.md?page=1)