age calculator

How to create an age calculator using Excel

Now that you know how to make an age formula in Excel, you can build a custom age calculator, for example this one:https://onedrive.live.com/embed?c

The image above is an embedded Excel Online sheet, so do not hesitate to input your birthdate in the corresponding cell, and you will discover your age in just a few seconds.

Calculators use these formulas to calculate age based on the date of birth in cell A3 as well as today's date.

  • Formula in B5 calculates age in years, months, and days:=DATEDIF(B2,TODAY(),"Y") & " Years, " & DATEDIF(B2,TODAY(),"YM") & " Months, " & DATEDIF(B2,TODAY(),"MD") & " Days"
  • Formula in B6 calculates age in months:=DATEDIF($B$3,TODAY(),"m")
  • Formula in B7 calculates age in days:=DATEDIF($B$3,TODAY(),"d")

If you're familiar using Excel Form controls, you have the option to calculate age on a particular date as illustrated in the screenshot below:

In order to do this, add two options buttons ( Developer tab > Insert > Form controls > Option Button), and link them to a cell. Also, create an IF/DATEDIF formula to get age as of today's date or at the time specified by the user.

The formula operates according to the following reasoning:

  • If the Today's date option box is selected, value 1 appears in the linked cell (I5 in this example), and the age formula calculates based on the today date:IF($I$5=1, DATEDIF($B$3,TODAY(),"Y") & " Years, " & DATEDIF($B$3,TODAY(), "YM") & " Months, " & DATEDIF($B$3, TODAY(), "MD") & " Days")
  • If the Specific date option button is selected AND a date is entered in cell B7, age is calculated at the specified date:IF(ISNUMBER($B$7), DATEDIF($B$3, $B$7,"Y") & " Years, " & DATEDIF($B$3, $B$7,"YM") & " Months, " & DATEDIF($B$3, $B$7,"MD") & " Days", ""))

Finally, nest the above functions in a way, and you'll get the entire age calculator (in the form of B9):
=IF($I$5=1, DATEDIF($B$3, TODAY(), "Y") & " Years, " & DATEDIF($B$3, TODAY(), "YM") & " Months, " & DATEDIF($B$3, TODAY(), "MD") & " Days", IF(ISNUMBER($B$7), DATEDIF($B$3, $B$7,"Y") & " Years, " & DATEDIF($B$3, $B$7,"YM") & " Months, " & DATEDIF($B$3, $B$7,"MD") & " Days", ""))

The formulas found in B10 and B11 work with an identical logic. Of course, they are considerably simpler, as they both contain only one DATEDIF function that returns age as the number of complete months or days, respectively.

For more information I encourage you to install the Excel Age Calculator and investigate the formulas within cells B9:B11.

Download Age Calcqulator for Excel

Ready-to-use age calculator for Excel

Users of our Ultimate Suite don't have to make an own age calculator in Excel - it's only two clicks away:

  1. Select a cell that you would like to add an age formula, go to the Ablebits Tools tab, then the Date & Time group, and then click the Date & Time Wizard button.
  2. It will begin the Date & Time Wizard will startand then you can go direct to page for age. tab.
  3. On the Age On the tab, you will find 3 items to be specified:
    • Birth date data as an individual cell reference or date in the mm/dd/yyyy format.
    • Age at the current date or an exact date.
    • Decide whether to calculate age in terms of days, months or years, or choose the precise age.
  4. Click the Insert formula button.

Done!

The formula is placed in the cell you have selected, and you double-click onto the handle for fill to paste the formula down the column.

If you've observed, the formula created in the Excel age calculator is more complex than the ones we've discussed so far but it does take into account singular and plural of time units like "day" and "days".

If you'd like to dispose of units that are zero like "0 days", select the Don't display zero units checkbox:
Calculate age ignoring zero units.

If you're interested to test the age calculator as well as to discover more time-saving extensions for Excel, you are welcome to download a trial Version of our Ultimate Suite. If you are impressed by the software and decide to get a license, don't miss this great deal exclusively for blog readers.

How to identify certain types of ages (under or above a particular age)

In some instances it is possible to not simply calculate age in Excel but also highlight cells that contain age ranges that are below or over a particular age.

If your age calculation formula yields the total number of years it is possible to design a regular conditional formatting rule that is based on a simple formula similar to these:

  • To draw attention to ages that are equal to or more than 18:
  • To highlight ages under 18: =$C2<18

C2 is the highest cell of the column called Age (not even including the header).

But what happens if the formula has age in years and months, or in years, months and days? In this situation you'll have to develop a rule basing it on a DATEDIF formula that calculates age from date of birth in years.

Supposing the birthdates are in column B starting with row 2, the formulas are:

  • To highlight ages under 18 (yellow):=DATEDIF($B2, TODAY(),"Y")<18
  • To highlight ages between 18 and 65 (green):=AND(DATEDIF($B2, TODAY(),"Y")>=18, DATEDIF($B2, TODAY(),"Y")<=65)
  • To show Ages that are over 65 (blue): =DATEDIF($B2 (TODAY (),"Y")>65

To create rules based upon the above formulas, select the rows, or the cells which you would like to highlight, go to the Home tab, then Styles group, then click Conditional Formatting > New Rule... > Create a formula to identify which cells you should format.

The complete steps can be found in this article: how to create an automatic conditional formatting rule that is based on formula.

This is the method you use to calculate age using Excel. I hope that the formulas were easy to understand and that you'll give them an attempt in your worksheets. Thank you for reading , and hope to see you on our blog next week!

Comments

Post a Comment

Popular posts from this blog

Parts Per Million (ppm) Converter

Converter Parts Per Million (ppm) Parts per Million (ppm) Converter for gases It is a converter calculates what volume of gas measured in ppm units and transforms it into quantities [mg/m 3] and the reverse. The unit ppm is employed in a variety of branches and ways. The use of ppm must be noted in fields below for input to determine how it will be converted into the proper unit. For more details on the use of ppm refer to the instruction below.In this section, there is an entry field for is the Molecular Weight you could either select from the drop-down menus or add the molecular mass of the substance. If you are unsure of the molecular value to you, then you should test our Molecular weight Calculator.When you enter your formula using either and the formula in units of [ppm] or units of [mg/m]. 3 The Value is transformed directly and is displayed at the lower left. This is how the importance of the number is calculated automatically. The significance is incre...
Read more

random number generator

Random Number Generator Utilize the generator to receive an unquestionably randomly digitally safe number. It generates random numbers that can be used when reliability of results is critical, like when shuffling a deck of cards during playing Poker and drawing number for giveaways, sweepstakes or lottery. What is the best method to pick the most random number between two numbers? You can use this random number generator for you to create a genuine random number from any two numbers. For instance, to get a random number within the range of 1 to 10 and even 10, you need to enter 1 first in the input and 10 in the next field, after which you press "Get Random Number". Our randomizer will select one of the numbers 1 to 10 random. To generate a random number between 1 and 100, use similar methods with 100, however, it falls within the second field on the randomizer. In order to simulate a roll of dice the range of numbers to be between 1-6 for a normal six-si...
Read more

BMI Calculator

BMI Calculator It's The Body Mass Index Calculator ( BMI calculator ) is an essential tool that can help you determine the body's weight. The calculator is able to ensure you're a healthy weight, this decreases the likelihood of getting weight-related ailments, like high blood pressure as well as heart issues related to type 2 diabetes and many other. What Is BMI (Body Mass Index)? BMI is an indicator measure that determines if you're overweight, overweight normal-weight or underweight for your weight. BMI evaluates your body mass to determine if your body mass falls to the healthy weight range. Along with height, BMI also depends on the age of your body and gender. It is possible to use the online BMI calculator to clarify the significance of. BMI is a measurement of an individual's leanness or corpulence, based on their size and weight. It is used to determine the mass of tissue. BMI is used to determine whether adults fall within the following cat...
Read more