# Excel question - sorting dates **URL:** \ **Category:** Factual Questions\ **Created:** [January 9, 2022, 7:33pm UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485 "2022-01-09T19:33:04Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![Mean\_Mr.Mustard](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mean_mr.mustard/32/3865_2.png) [@Mean\_Mr.Mustard](https://boards.straightdope.com/u/Mean_Mr.Mustard)\ **Post date:** [January 9, 2022, 7:33pm UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/1 "2022-01-09T19:33:04Z")
I am a very basic Excel user, so keep that in mind. 🙂 I have created a spreadsheet of all family birthdays and anniversaries. I want to be able to sort it chronologically, but also, sometimes, like a calendar. For example: January 2, 1966 January 4, 2004 January 3, 2018 or: January 2, 1966 January 3, 2018 January 4, 2004 How would I set up the columns to be able to accomplish this? Thanks! mmm ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [January 9, 2022, 7:36pm UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/2 "2022-01-09T19:36:34Z")
Split the dates into 3 columns - year, month and date. ---
**Author:** ![Mean\_Mr.Mustard](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mean_mr.mustard/32/3865_2.png) [@Mean\_Mr.Mustard](https://boards.straightdope.com/u/Mean_Mr.Mustard)\ **Post date:** [January 9, 2022, 8:00pm UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/3 "2022-01-09T20:00:49Z")
Thanks, much, Munch! ---
**Author:** ![OldGuy](https://avatars.discourse-cdn.com/v4/letter/o/3bc359/32.png) [@OldGuy](https://boards.straightdope.com/u/OldGuy)\ **Post date:** [January 9, 2022, 8:22pm UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/4 "2022-01-09T20:22:17Z")
You really only need two columns I think MM/DD and YYYY. The YYYY should contain the correct date. Th first column should be mm/dd/2000 making sure all have the same year. Then sorting on that column will sort like a calendar. You can hide the year by choosing the formatting of the column. Similarly you can hide the mm/dd in the last if you wish. ---
**Author:** ![Joey\_P](https://avatars.discourse-cdn.com/v4/letter/j/919ad9/32.png) [@Joey\_P](https://boards.straightdope.com/u/Joey_P)\ **Post date:** [January 9, 2022, 8:56pm UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/5 "2022-01-09T20:56:36Z")
This one always takes me a few tries. First off, make sure Excel knows that they’re dates as opposed to text or something else, then try sorting it again. If that doesn’t work, tell Excel to change the format from MM/DD/YYYY to YYYY/MM/DD and that should do it. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [January 9, 2022, 11:24pm UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/6 "2022-01-09T23:24:38Z")
That won’t do it. He also want to sort by month when he needs to. ---
**Author:** ![FinsToTheLeft](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/finstotheleft/32/2930_2.png) [@FinsToTheLeft](https://boards.straightdope.com/u/FinsToTheLeft)\ **Post date:** [January 10, 2022, 12:45am UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/7 "2022-01-10T00:45:15Z")
If you keep the main column as an Excel date, you can use eg `=YEAR(A2)` to pull out the date portion you want. ---
**Author:** ![Joey\_P](https://avatars.discourse-cdn.com/v4/letter/j/919ad9/32.png) [@Joey\_P](https://boards.straightdope.com/u/Joey_P)\ **Post date:** [January 10, 2022, 2:12am UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/8 "2022-01-10T02:12:33Z")
> [@Munch](#): > > That won’t do it. He also want to sort by month when he needs to. I think if you told excel to change it back to MM/DD/YYYY, then changed the cells to be text, you could do it. Excel would sort them alphabetically, which would group each month together (and within the month, further sort them by day). But, as others said, putting each part of the date in it’s own column would probably be the most practical, especially if this gets done on a regular basis. But I’d probably do it as three columns just for a bit of future proofing. ---
**Author:** ![Princhester](https://avatars.discourse-cdn.com/v4/letter/p/3e96dc/32.png) [@Princhester](https://boards.straightdope.com/u/Princhester)\ **Post date:** [January 10, 2022, 2:34am UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/9 "2022-01-10T02:34:10Z")
But you’d also want to then make it a table, because otherwise an accidental sort on only one of the three columns will jumble your dates. ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [January 10, 2022, 5:51am UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/10 "2022-01-10T05:51:59Z")
In my view you should never drop information from a database unless necessary. So I’d keep three columns DD MM and YYYY (don’t drop the century) I’d also retain a separate date display column. > [@Joey\_P](#): > > make sure Excel knows that they’re dates as opposed to text or something else Now this is the real trick with using Excel for family or genealogical work. Excel dates are numbers. Excel day 1 is Sunday, 1 January 1900 Excel day 0 is Saturday, 0 January 1900 There are no negative dates. So if great, great grandma was born in 1896 Excel dates won’t work. But with the three columns and a month-\> month name vlookup you can get Excel “dates” in month/year, month/day/year, year/month/day or any other format going back to 0 AD. ---
**Author:** ![puzzlegal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/puzzlegal/32/3827_2.png) [@puzzlegal](https://boards.straightdope.com/u/puzzlegal)\ **Post date:** [January 10, 2022, 6:21am UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/11 "2022-01-10T06:21:01Z")
I would have two columns. The first would have the true date. Your can use whatever format you want. Excel knows it’s a number and will sort it from oldest to youngest (or vice versa) The second column would point to the first column, and would have text(month(date), “00”)&”-”& text(day(date), “00”) So, if the date was Jan 4, 1956, the second column would show 00-04. If you sort by the second column you’ll get the calendar order. (I’ll check the exact format tomorrow) ---
**Author:** ![Mean\_Mr.Mustard](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mean_mr.mustard/32/3865_2.png) [@Mean\_Mr.Mustard](https://boards.straightdope.com/u/Mean_Mr.Mustard)\ **Post date:** [January 10, 2022, 10:36am UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/12 "2022-01-10T10:36:21Z")
> [@penultima\_thule](#): > > In my view you should never drop information from a database unless necessary. > So I’d keep three columns DD MM and YYYY (don’t drop the century) > I’d also retain a separate date display column… This is what I ended up doing, seems to work fine so far. mmm ---
**Author:** ![Mean\_Mr.Mustard](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mean_mr.mustard/32/3865_2.png) [@Mean\_Mr.Mustard](https://boards.straightdope.com/u/Mean_Mr.Mustard)\ **Post date:** [January 10, 2022, 10:39am UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/13 "2022-01-10T10:39:45Z")
Ok, another question. Within this form I have a column for the age of the person (or year of anniversary) in 2022. When 2023 rolls around, is there a way I can automatically add one year to an entire column of numbers? Or should I just do it manually. mmm ---
**Author:** ![naita](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/naita/32/5862_2.png) [@naita](https://boards.straightdope.com/u/naita)\ **Post date:** [January 10, 2022, 11:32am UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/14 "2022-01-10T11:32:17Z")
Use datedif between the year in the birth date and the current year =datedif(today(), year(cell\_reference\_to\_birthdate), y) Or something very similar. I don’t have excel on this machine to test it. ---
**Author:** ![Retzbu\_Tox](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/retzbu_tox/32/11829_2.png) [@Retzbu\_Tox](https://boards.straightdope.com/u/Retzbu_Tox)\ **Post date:** [January 10, 2022, 2:11pm UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/15 "2022-01-10T14:11:45Z")
Naita is on the right track. Where _A2_ appears in the following Excel formula, substitute a reference to the cell in your spreadsheet where you have a birthdate. =DATEDIF(A2,TODAY(),“y”) ---
**Author:** ![naita](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/naita/32/5862_2.png) [@naita](https://boards.straightdope.com/u/naita)\ **Post date:** [January 10, 2022, 3:16pm UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/16 "2022-01-10T15:16:16Z")
It’s possible I misunderstood mmm or you or exactly what your formula will do. I thought mmm wanted a column showing that “This year John Doe has had or will have his 57th birthday” as the numeral 57. And I thought, if A2 is the full date, it will instead show John Doe’s conventional age and roll over on actual birthday. ---
**Author:** ![puzzlegal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/puzzlegal/32/3827_2.png) [@puzzlegal](https://boards.straightdope.com/u/puzzlegal)\ **Post date:** [January 11, 2022, 2:39am UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/17 "2022-01-11T02:39:55Z")
Type your actual birthdates in the first column 1/2/1966 1/3/2018 1/4/2004 6/1/2002 11/1/1988 In the second column, create a formula pointing to the first column that says =month(xx)&"-"&Day(xx) That will give you 1-2 1-3 1-4 6-1 11-1 Now, if you sort by the first column, you get the dates from oldest to youngest, and if you sort by the second column (selecting the option “treat things that look like numbers as numbers” when it asks) it will sort it by the calendar. If you don’t want to get asked every time you sort, then zero-fill the numbers =text(month(xx),“00”)&"-"&text(day(xx),“00”) giving you 01-02 01-03 01-04 06-01 11-01 If you want the age, put today’s date (or any date of interest) somewhere else in the spreadsheet. And create a third column, age =floor(yearfrac(xx,TODAY),1) That will show, for 1/1/2022 55 3 17 19 33 If you want the age to update every time you open the spreadsheet, you can set that fixed date to be =today() and it will always show today’s date, and calculate the age as the difference between the date you are looking at the spreadsheet and the birthdates. There are dozens of ways to get the age, but this is an easy one. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [January 11, 2022, 3:01am UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/18 "2022-01-11T03:01:52Z")
> [@puzzlegal](#): > > =text(month(xx),“00”)&“-”&text(day(xx),“00”) Can also be done as `=TEXT(A1, "mm-dd")` ---
**Author:** ![puzzlegal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/puzzlegal/32/3827_2.png) [@puzzlegal](https://boards.straightdope.com/u/puzzlegal)\ **Post date:** [January 11, 2022, 3:13am UTC](https://boards.straightdope.com/t/excel-question-sorting-dates/957485/19 "2022-01-11T03:13:49Z")
Yes, that’s much cleaner. I do do much formatting of numbers that aren’t dates that i overlooked that. 😀