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.

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.

## 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):

 1 =INT(YEARFRAC( C2;NOW()))

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.

 1 =!Family!C2

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

 123 =IF(isBlank(A1);"";IF(DATE(YEAR(TODAY());MONTH(A1);DAY(A1))>TODAY(); DATE(YEAR(TODAY());MONTH(A1);DAY(A1)); DATE(YEAR(TODAY())+1;MONTH(A1);DAY(A1))) - TODAY())

I used this formula to show their age transformation:

 1 =IF(ISBLANK(Family!D2);"";INT(Family!D2) & " > " & Family!D2 + 1)

Example output:

 1 23 > 24

## 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.

 1234567 function onOpen(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets(); //Note that this is the second tab var tableRange = "A2:K1000"; // What to sort. var range = sheet.getRange(tableRange); range.sort( { column : 2, ascending: true } ); }

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 