Set age group based on DOB

Question

I would like to create a Google Sheet function that if supplied with a date of birth, looks up the below table and gives the appropriate age group. I will be using this table to group 200 kids for surf lifesaving.

for example...date of Birth is 11 Oct 2011, then the age group will be U7. I have use the query function to get this data but I would like to contain it in a function and store the table as a array and compare the date to the array table.

- U 5   01 Oct 2013  30 Sep 2017
- U 6    01 Oct 2012     30 Sep 2013
- U 7    01 Oct 2011     30 Sep 2012
- U 8    01 Oct 2010     30 Sep 2011
- U 9    01 Oct 2009     30 Sep 2010
- U 10   01 Oct 2008     30 Sep 2009
- U 11   01 Oct 2007     30 Sep 2008
- U 12   01 Oct 2006     30 Sep 2007
- U 13   01 Oct 2005     30 Sep 2006
- U 14   01 Oct 2004     30 Sep 2005

Answer 1

Dates can be a little tricky. See This question to get a better idea. But this script can at least get you started.

This Google Sheet demonstrates a custom formula (AGEGROUP) to compare the input date to a "age-groups" sheet.

Note: Only add to "age-groups" sheet if needed. The last row with a value is used to consider if someone is too old. The first row of the sheet is used to see if someone is too young.

AGEGROUP() will accept a cell reference to look up

Example AGEGROUP(B2)would check cell B2, assuming it contains a date, and return a group assignment (U 5, U 6, U7...).

Looking up dates manually (instead of referencing another cell) can be accomplished by nesting DATE inside of AGEGROUP. You must follow the right arguments for DATE (year, month, day).

Example AGEGROUP(DATE(2010,1,21))

I'm new to custom functions and did not look into the options for supporting function details like @param or @return and so on.

Apps Script

/**
* Finds age group of the input value (date).
*
* @param {date} Input the date to compare to date ranges.
* @return Returns age groupd based on provided date.
* @customfunction
*/

function AGEGROUP(date) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var ages = ss.getSheetByName("age-groups");
  var range = ages.getDataRange();
  var values = range.getValues();

  var i = 0;
  var ranges = values.length;
  var lastRow = ranges -1;

  // loop through dateRanges
  for(i; i < ranges; i++) {   

    // if date in AGEGROUP()
    if(date >= values[i][1] && date <= values[i][2]) {
      // return age group
      return values[i][0];
    }

    // if child is too old
    if(date > values[0][2]) {
      return "Child is too young.";
    }

    // if child is too young
    if(date < values[lastRow][1]) {
      return "Child is too old.";
    }
  }
}