## Calculating correct age for competitions with a cut off date

Published on September 9, 2014 by in Excel Functions | Useful, Excel | Case Studies

Got presented with an interesting dilemma recently. If you (or your children or any children you know) compete in anything there is usually an age cut off point. For example my son competes in music competitions and the cut off date for each year is 1st January i.e. if you are 17 on the 1st January of the current year, then you are competing in the 15-18 category. Even though at the time of the initial competitions (usually May) you may actually be 18.  So if you just had one child, that would be find but if you were running a class, that could be more problematic.

So this is what I did. Here is the file: age-calculation-with-cut-off

I set up a helper cell to show the cut off point. (that’s in cell D1)

I used the formula:

=DATE(YEAR(TODAY()),1,1)

Essentially, this will always show the current year: Year(today()), the Month number (1 in this case) and Day (1 in this case again). But if the cut off changed, you could change the day and month here. e.g. if the new date was 12th February, your revised formula would read as follows: =DATE(YEAR(TODAY()),2,12)

Then I used the lesser known DateDif function to calculate the difference between the date of birth  and the cut off point in years.  I put this formula in cell B3 and copied it down.

=DATEDIF(A3,\$D\$1,”y”)

A3 = date of birth

\$D\$1 = cell with cut off date

“y” = shows the answer in years.

I also added in a vlookup function which referenced the correct competition a child should be entered into. This looks up the child’s age. Then compares it to an age on the Competition Look-up sheet and returns the second column i.e. Competition Category.

## Excel Products for Sale

Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.

## MrExcel.com

Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!)  that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.

## MyExcelOnline.com

### Xtreme Pivot Tables course

This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.

He also has a useful Pivot Table webinar…

This is also a useful chart helper.

## Debra Dalgleish – Contextures.com

Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of \$10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.

#### Excel UserForms for Data Entry

The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.