Keep Family Birthdays And Addresses In Order Using Google Spreadsheets

I created a Google Spreadsheet because my wife has a large family we keep some information organized. I wanted to automate some things like who’s birthday is next and what age he/she will turn into. I also made an example spreadsheet to show how it’s done.

The data that has to be manipulated.

This can be made in Excel and Google Spreadsheets. I chose Spreadsheets because I also wanted to experiment with the language Go behind Google Spreadsheets.

Result

Data First

So let’s start with basic information and things like calculate age and years of marriage.

We want to make a basic spreadsheet where we store the family information, and things like age and wedding years will be calculated. Formula’s for that are like this:

the input will be a cell that contains a date like C2 (02-02-2012):

Tip: use the fill handle to put the formula in all cells in the row.

 Who’s birthday is next?

Then we can create a list of when someone’s birthday is. I created a new tab inside the sheet. !Family! is used to refer to data of the other tab.

To calculate how many days it will take when someone’s birthday is again we use the following formula.

I used this formula to show their age transformation:

Example output:

Automatically order the birthday’s through Google’s script

For the new tab we want to order by upcoming birthday’s. This can be done in the code behind the spreadsheet. Click Extra > Script editor.

When this speadsheet opens this code will run and will automatically sort on days remaining. Use this spreadsheet to your advantage and feel free to edit into your needs.

Tips and tricks

When you copy paste the formula, you will need to change the references you use inside the formula. I recommend using the fill handle function at most times.

Closing

See the example live at: https://docs.google.com/spreadsheets/d/1ktw71fUbyPyfl6-qR0-EfL2P0SQREoaV5hLryK9AmT8/edit?usp=sharing.