age calculator

How can I 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 you can enter your birthdate within the appropriate cell and you'll be able to determine your age within a matter of seconds.

The calculator utilizes the following formulas to calculate age according to the age of the date of birth in cell A3 as well as the current 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've worked using Excel Form controls, you have the option to compute age at a certain date as shown in the following screenshot:

For this, add a couple of option buttons ( Developer tab > Insert > Form controls > Option Button) as well as link them to some cell. And then, write an IF/DATEDIF calculation to determine age that is at present or on the date indicated by the user.

This formula follows the following logic:

  • 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", ""))

Last but not least, put the above functions together, and you'll be able to get the complete age calculator (in 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 that are in B10 and B11 are based on the same logic. Of course, they are much simpler because they include just one DATEDIF function to calculate age as the total number of months or days, respectively.

To learn the details, I invite you to install the Excel Age Calculator and investigate the formulas that are found in cells B9:B11.

Download Age Calcqulator for Excel

Useful and ready-to-use age calculator for Excel

Users of our Ultimate Suite don't have to bother about making an own age calculator in Excel - it is only a couple of clicks away:

  1. Select a cell that you'd like to include an age formula, go to the Ablebits Tools tab and then click the Date & Time group, and then click the Date and Time Wizard button.
  2. It will begin the Date & Time Wizard will begin, and you'll be taken direct to age tab.
  3. On the Age Tab, there are 3 options to choose from:
    • Birth date data as a cell reference or a date in the mm/dd/yyyy format.
    • Age at the current moment or specific date.
    • Choose whether you want to calculate age in days, months year, or even absolute age.
  4. Click the Insert formula button.

Done!

The formula is placed in the cell you have selected before you click your fill button to duplicate it into the column.

You may have noticed, the formula formulated from our Excel age calculator Excel age calculator is more complex than the ones we've been discussing but it also accommodates singular and plural of time units, such as "day" and "days".

If you'd prefer to get rid of zero units , such as "0 days", select the Do not display zero units checkbox:
Calculate age ignoring zero units.

If you're eager to check out the age calculator as well as to discover 60 more time-saving tools that can be added to Excel and Excel, we invite you to download a free trial edition of the Ultimate Suite. If you're impressed with the tools and are able to buy a license, make sure you don't miss this exclusive offer only for blog readers.

How do I highlight certain age groups (under or over a specified age)

In some instances, you may need not just calculate age in Excel however, you may also want to highlight cells which contain aged numbers that are lower or above a certain age.

In the event that your age calculation formula is able to calculate the number of years that are complete that you have, you can design an ordinary conditional formatting rule built on a basic formula, like the following:

  • To highlight ages equal to or over 18:
  • To highlight ages under 18: =$C2<18

Where C2 is the top-most cell of the column called Age (not including the column header).

But what happens if your formula will display age in years and months, or in years, months and days? In this situation you'll have to create a rule based on a DATEDIF formula which calculates age from date of birth in years.

Supposing the birthdates are in column B that begin 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 draw attention to Ages beyond 65 (blue): =DATEDIF($B2, TODAY (),"Y")>65

To create rules based on these formulas, simply select the cells or entire rows you'd like to highlight, go to the Home tab, then Styles group, and click the New Rule button... > Apply a formula to identify which cells to format.

The steps in detail can be found here: How to create the conditional formatting rules that is based on formula.

This is the method you use to calculate age within Excel. I hope the formulas are simple for you to grasp and you will give them an attempt in your worksheets. Thank you for your time and we hope to see you again here next week on our blog!

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. This converter calculates how much the amount of the gas's volume measured in ppm units and transforms it into the units [mg/m 3[mg/m3] and the reverse. The unit ppm is used in a myriad of branches, in different ways. The usage of ppm has to be specified within the fields below to input, to allow it to be converted into the appropriate unit. For more information about the use of ppm, look at the manual below.In the field labeled "Molecular Weight, you will find the entry field to this field. Molecular Weight can be selected from the drop-down menu or put in the molecular mass within that gas. If the molecular weight isn't known for you, then use our molecular-weight Calculator.When you enter the formula or one of the and the formulas, either the formula in units of [ppm] or units of [mg/min units of [mg/m 3[mg/m] 3. The value is converted immediately and displayed at the...
Read more

Random Number Generator

Random Number Generator: How Do Computers Generate Random Numbers? People have been using random random numbers for many millennia. The idea isn't brand new. From the lottery of the earliest days of Babylon to Roulette tables at Monte Carlo, to dice games in Vegas, the goal will be to give the final result up on random chance. But gambling aside, randomness has many uses in the fields of science, statistics, cryptography, and so on. However, using coins, dice or similar materials as a random device comes with its own limitations. Because of the mechanical nature of these methods, generating large quantities of random numbers requires a lots of time and effort. With the help of human innovation, we have more efficient instruments and techniques available to us. Methods for generating random numbers True Random Numbers Photograph of analog-input digital-output processor device. Photo by Harrison Broadbent Let's take a look at two methods that are us...
Read more