Today is a quickie post for something handy I learned. I’m writing a story about vampires who live for multiple centuries, so sometimes it’s difficult to keep their ages straight as the story goes through time. I came up with a simple method in Google Sheets, but this likely will work with Excel too. While I used this for immortal vampires, it could be handy for normal humans, or plot events you need relative time scales for.
As you see here, I’ve got various historical & plot events, as well as my characters’ bolded names with the years they were born. For dates I was unsure of the month/year, I just set to January 1st. In the columns, we see the ages in years that will be calculated. To begin, you’ll want to set up your file with the important dates, labels, and then columns for the characters/events you want to age. Freeze the top row with the labels by going to View > Freeze > 1 Row. Now right click the column with your dates, and select: Sort Sheet A to Z. This will reorder the dates chronologically. Your file should look something like this:
Next, we will enter a formula in the first cell, as you see on the very top of the image below. This formula will not work in the negative, so start with the birth date of the character, but not before.
I use this code: =DATEDIF($A$1,A2,”Y”)
“Y” stands for ‘year,’ and the A is for the column the dates are in, the 1 is for the row of the birth date of the character I’m aging, and the 2 is for the date on the current row. For example, if the character’s birth date is on row 43, and the current row that I want to calculate the age for is 46, I would use the code: =DATEDIF($A$43,A46,”Y”) The first cell should read ‘0’, as that’s the character’s age the year they were born. Next, I can click the dot on the bottom right corner of the cell, and drag it down to populate the rest of the column.
As you see here, A43 is the character’s birth date, and A48 is the current row, showing the character would be 12 years old during the event, and 18 in the next.
The $ is optional, but keeps the variables from changing when dragging, which is handy when filling out a long column. One problem with this method is that if you re-order the rows, (such as adding new dates in and then arranging them chronologically), you will need to fix the original code and drag the formula out again. Make sure the updated formula has the correct row for your character’s birth date. If you foresee adding lots of new dates as you go, use this formula: =DATEDIF(A1,A2,”Y”). This way the variables will update as you go, though you will have to paste and update it manually in each cell rather than dragging it out.
I hope you can make good use of this technique, and let me know if you have a better way as I’m not a Google Sheets master… yet.
Cubist Vowels 