You are rushing to get your niece a birthday card before her birthday, and you cannot remember how old she is, right? Also, is your grandson in second or third grade this year?
I created a spreadsheet to help with all those hard-to-remember facts about my family.
This only works for birthdates beginning January 1, 1900. Excel (and Google Sheets) will not do “date math” on any dates before the 20th century.
For those of you who just want the spreadsheet, but you do not care about the hows or whys, and you trust me completely, you can download it here:
DO NOT delete anything in Column C. Just type over the names in Column A, birthdates in Column B. Delete or type over the notes in Column D. Excel will add include expand its table as you type names in Row 11.
I never recommend downloading a file from someone you do not know, so for this rest of you, here is a lesson that will help you learn about formatting dates.
Create List of Names and Birthdates
At the top, I always like put the name of the spreadsheet in Cell A1, so I have called my spreadsheet “Birthdays and Ages.”
There are four columns: Name, Date of Birth, Age on Birthday, and Notes. Go ahead and put those terms in Cells A3 through D3.
Now under “Name” put the names of your closest relatives whose birthdays you want to track. (I am going to use my pets as a demonstration – you can include your fur-babies, too.) I have also included a few of my deceased loved ones, because I often wonder how old they would have been.
Two-Digit Year vs. Four-Digit Year Problems
Some of your family may be older. Now that we are in the 1900s and 2000s, I recommend you use all four-digits when you enter a date. I have created some older family members, so you can see the problem. Amy Byrne was born in 1923. If I enter the date as “7/1/23,” Excel will think Amy was born in 1923.
As we get closer to mid-century, problems with two-digit years are going to be a continuing issue with two-digit year’s. Our generation’s “Y2K bug.” First we will format our list as a table, then we will figure out how to solve the year problem.
Format as Table
We need to select our table’s range by dragging through our table, from the “Name” header in Cell B3 to the last entry in Column D. For my list that is Cell D10. (For more about Formatting as a Table, see https://helpfulofficetips.com/2012/02/09/excel-2010-why-should-i-use-format-as-table-command-when-my-table-is-already-there/.)
Now on the Home tab>Styles section>select the Format as Table drop-down arrow, and select your favorite table format. I often print this list out, so keep away from the Medium style section.
The Create Table window will pop-up with your range selected. IF you included your headers, Excel will have already checked “My table has headers,” and simply click OK. IF you DID NOT include your headers, click Cancel and start again.
Let’s format Column B for two-digit month, two-digit date, and four-digit year.
Move your cursor over the “B” at the top of Column B. The cursor will turn into a thick black arrow. Click on the “B” to select the entire column. When Column B is selected, it will all be gray.
From the Home tab>Numbers section>select the drop-down arrow next to the “General” format. A drop-down menu will appear. Select the very last item More Number Formats.
You can also select the “More Choices” icon at the bottom right of the Home tab>Number section. There are always multiple ways to perform functions in Excel.
The Format Cells window will appear. Usually if you select a cell that is a date, Excel would have already guess you wanted dates, but our Column B contains dates, empty cells, and text, so Excel is keeping in “General” format and not making a guess.
In the Format Cells window, click on the Date option under Type in the left-hand navigation pane. Several date options will appear, but none of them are two-digit month, two-digit date, and four-digit year, aka, “mm/dd/yyyy!” We will need to enter a custom date format. I already gave you hint for formatting dates, do you see the hint?
Excel date formats use “m” for month, “d” for date and day of the week, and “y” for year. Here are some examples:
Excel likes to use the example of March 14, 2012 as an sample for its date formats:
|Format Symbol||Cell Value or Text||Definition|
|m||3||one-or two-digit month number without leading zero|
|mm||03||two-digit month number with a leading zero if required|
|mmm||Mar||three-letter month abbreviation|
|mmmm||March||full name of month|
|d||12||one-or two-digit date number without leading zero|
|dd||12||two-digit date number with a leading zero if required|
|ddd||Wed||three-letter day of the week abbreviation|
|dddd||Wednesday||full name of day of the week|
|yy||12||last two-digits of the year|
|yyyy||2012||four digit year|
Time is similar with “h” for hours, “m” for minutes (confusing, right?), and “s” for seconds, separated by colons, such as 12:45:30 for be 12:45 and 30-seconds. Those colons tell Excel if you mean “m” for “months” or “m” for minutes. You can also add “am” or “pm.” Let’s get back to our less confusing dates!
Notice in the Excel table above, the “values” of the dates all line up on the right of the cell because Excel views them all as number values, even if we see the word for “Wednesday.” Excel simply returns the value for the day of the week on that date; the rest of the date is hidden in the background.
And that is how we can manipulate dates and figure out the ages of our family on the Birthdates and Ages spreadsheet. Excel can apply simple arithmetic to those dates because each date is a value; the actual value is the number of days since January 1, 1900.
Four-Digit Date Format
Back to formatting Column B. In the Format Cells window, select the last item in the Category list on the left, called Custom. Here is where those funny numbers and letters show up. The format we want is not there, so drag through the word “General” in the “Type” box.
Now type the format we want, which is
Then press, okay, and voila! All our birthdates are “mm/dd/yyyy” format. Now your grandparents’ birthdates are in the correct century. If you need to make any corrections, make them now. Whew!
Make It Pretty
Now clean up your spreadsheet. Go through a make any formatting changes such as double-clicking between Column A and Column B to make the column wider so you can see your family’s names. (You can also hover between Column A and B and then drag the stretch cursor to the right until you see each name in full.)
Let’s make that Notes column bigger, too. Drag between Column D and Column E so that you have a few inches to work with for your notes.
Notes sometimes need to have “Text Wrap” enabled if you are typing a paragraph. Select Column D and in the Home tab>Alignment section>click Text Wrap. You can make sure this worked by typing a few lines in Cell D4 and pressing Enter.
(Before I finish my spreadsheet, I will probably use “text- wrap” of the Age of Birthday column so it is a bit nicer looking.)
What Year is It?
Now Excel needs to know what year it is right now! In Cell D1, type the today formula for today’s date, and press enter to enter the formula in the cell.
Today’s date will show up in your current default format… BUT for this spreadsheet we are dealing with ages, so we only want the year. Now we are going to add the =Year() formula with our today formula within the parentheses. Huh?
Click back on Cell D1, and look at the Formula Bar. Click between the “=” and “Today.” We want to only have the “year” of today’s date, so type “YEAR” and add an opening parenthesis. It should look like this:
Before you press Enter, add an ending parenthesis to finish our formula. If you forget, Excel will ask if you want Excel to add it for you. Say, “Yes!”
Every time you open your spreadsheet the current year will appear in Cell D21.
Name a Cell
We are going to name Cell D1 “ThisYear” so we do not get confused. This will make all our formulas easier.
Click back on Cell D1, so you see our fancy formula in the Formula Bar. Now look to the left of the formula bar in the address box where it shows D1.
Drag through “D1” and type “ThisYear” – Excel likes “range names” to be one word. Upper and lowercase do not matter in range names.
Press Enter. Click somewhere below your table.
Way back in the Visicalc and Lotus 123 ages, we did not have a mouse, so we needed shortcuts to get around our spreadsheet. By pressing the F5 function key at the top of your keyboard, you could bring up the GoTo window and “go to” any cell.
Press the F5 button. When the Go To window appears, you will see all our named ranges. Our table is called “Table1” and Cell D1 is named “ThisYear.” Double-click on ThisYear, and your cursor will jump to Cell D1. (You can type any cell reference in the GoTo window, and Excel will take you there.)
Now We Do Some Date Arithmetic
In your table, click on the first blank under “Age at Birthday,” Cell C4. How do we start any formula? Type an “=” sign to get things rolling. We will start with our year in “ThisYear” so click D1, and Excel will put “ThisYear” in the formula. (From now on, Excel will always called D1 by its assigned name “ThisYear.) Later when we copy this formula, it will always look to “ThisYear” no matter what column or row we are in. This keeps that cell reference “Absolute.” (“Relative” cell references move up or down row by row or column by column as we copy and paste.)
Now enter a minus “-“ sign because we are going to subtract the year of the birthdate from ThisYear. We will use that =YEAR() formula again to get the year your family member was born. After the minus type YEAR with an opening parenthesis “(“ then click on the first birthdate in Cell B4. Click our closing parenthesis and press enter. The formula should look just like this:
Ready to press Enter? Watch what happens in your table.
Once you have a table in your spreadsheet, Excel will try to fill out the table for you. As soon as you hit Enter, Excel puts your formula in Cell C4, then copies it into all the cells within Column C to the bottom of our table, Cell C5, Cell C6, and so on.
Dates? Why Are There Dates in the Age Column
Oh, no! Excel filled out all of Column C with strange dates instead of the actual age of each family member. Huh? When Excel does arithmetic with dates and =TODAY() or =YEAR(), it figures your answers should be in date format. We can change that with a quick click.
Select all of Column C by clicking on “C.” Now go back to the Home tab>Number section, and change the formatting to Number instead of Date.
Now those odd dates will transform into an age in years… but with two decimal places.
We do not want those decimals in our years, so click the Home tab>Number section>Decrease Decimals icon a twice to remove the tenths and hundredths.
Now we know the year each family member will turn on their birthday “ThisYear.”
I think I will delete my notes entry about “the quick brown fox” before I save my spreadsheet by clicking on Cell D4 and pressing Delete.
I find this column is handy for writing in the grades of my children along with the school year, such as “21-22 Grade 9” or something similar. When my mother passed away, I entered the day she died and how old she was: “Died 01/08/2008 at 82.” That information comes in handy, plus in the Age column, I can still see how old she would have been on her upcoming birthday.
Organizing Dates by Calendar
One last little trick. You may have not entered your dates in the order they appear on your calendar. (You should have.)
Once your Birthdays and Ages spreadsheet is set, you can easily add a new addition to your family by inserting a row in the correct spot by birthday, typing in the information, and copying the Age formula from one cell to the new cell. When you insert a blank row, Excel will automatically copy the formula, BUT NOT the correct “General” format to your Age cell. A simple copy & paste will fix this, since paste always copies the formula AND the formatting.
Sort by Month and Day
But what if your dates get mixed up? We need a trick, because if Excel sorts our dates chronologically, it sorts youngest to oldest or oldest to youngest. We need them sorted within the calendar, by month and day.
Here is a trick I learned from: https://turbofuture.com/computers/Sort-Dates-By-Month-and-Day-Ignoring-Year. (I added a period between the month and date in the formula below to help you see what we are doing.) We are going to trick Excel into sorting the dates without the year!
Go to the right edge of your spreadsheet, and click on Cell E4. Enter this magical formula clicking on Cell B4 when needed:
Excel has named all the cells in the table, so instead of Cell B3, Excel calls this “[@Birthdate].” This means it looks to the Birthdate column in the current row of the table.
When you click Enter, Excel will automatically add a new column to your table. In this column, Excel will magically transform each birthdate into just the numbers for the two-digit month and two-digit date with a period between. With the “=TEXT()” formula we are telling Excel to transform those numbers into a text entry or a “word” that can be alphabetized. The first part after the parenthesis is which cell to use, and the second part is the format to use. When you sort the numbers of the months March (03) and December (12) the “03” will come first in alphabetical order.
When Excel added that column, it also added a sort/filter drop-down and named the column “Column1” since we did not give it a name. Click on Cell E3, and type “Calender Order” to give it a name, and press Enter. That way we should remember why that cell is there.
Now, click on the Sort/Filter drop-down. It does not say “Smallest to Largest” because these are the “text” equivalents of the month and days, not a number “value.” If they were values, the Sort/Filter choices would be “Smallest to Largest” or “Largest to Smallest.” Instead let’s sort them “A to Z.”
Now they are in order by calendar date. You might want to fill them in on your calendar!
Adjust Print Area
We do not want this column to print with the rest, so let’s move it off the printed page. In the View tab>Workbook Views section, select Page Break Preview.
We will adjust the bottom right corner of your Print Area, shown by the heavy blue lines. Hover your mouse over that dark blue corner until the stretch arrow cursor appears. It should be pointing diagonally. Drag the blue print area to the bottom of your table BETWEEN Column D and Column E.
Now Column E will not print. Since Column E and beyond are outside the Print Area, you can write yourself a reminder about the Calendar Order and why it is there. None of that area will print. I usually skip one column so that Excel does not try to put the column into the table, then I make the cell text bold and red! (Yes, I could also place a note inside the cell with the Review tab>New Comment, but that is a whole other lesson.)
To return to Normal view, click on the small grid near the bottom right of your screen. That is the “normal” shortcut. You can also click View tab>Workbook Views>Normal.
Save and Exit
Before my final save, I always look over the spreadsheet for readability.
I would use Home tab>Alignment section>Text Wrap to wrap the Age at Birthday column to be narrower.
I would also increase my font size size one or two sizes to make it easier to read. If the birthdates turns into “#####” when I make the font bigger, it means I need to increase the column size with a quick double-click between Columns B and Columns C.
Now, it is ready for saving and printing, I usually print this out every January and keep it hanging behind this year’s calendar.