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
Split the dates into 3 columns - year, month and date.
Thanks, much, Munch!
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.
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.
That won’t do it. He also want to sort by month when he needs to.
If you keep the main column as an Excel date, you can use eg
=YEAR(A2)
to pull out the date portion you want.
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.
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.
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.
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.
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)
This is what I ended up doing, seems to work fine so far.
mmm
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
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.
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”)
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.
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.
Can also be done as
=TEXT(A1, "mm-dd")
Yes, that’s much cleaner. I do do much formatting of numbers that aren’t dates that i overlooked that. 