age calculator

How do 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

What you see in the above image is an Excel Online sheet, so feel free to enter your birthdate into the appropriate cell, and then you'll discover your age in a flash.

The calculator employs the following formulas to compute age basing it on the "" date of birth in cell A3 and 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 have some experience working with Excel Form controls, you have the option to calculate age on a particular date such as in the following screenshot:

To do this, you need to add two buttons for options ( Developer tab > Insert > Form controls > Option Button) as well as link them to some cell. Also, create an IF/DATEDIF formula that will give you age or at the time of today's date or at the date specified by the user.

The formula is based on 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", ""))

Then, you can nest the above functions into each other, then you'll get the complete age calculation formula (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 used in B10 and B11 use similar logic. Of course, they're considerably simpler, as they both contain just one DATEDIF function that returns age as the total number of days or days, or both.

For more information for the details, I recommend you get this Excel Age Calculator and investigate the formulas used in cells B9 and B11.

Download Age Calcqulator for Excel

Ready-to-use age calculator for Excel

Our users of the Ultimate Suite don't have to think about creating an own age calculator in Excel - it's just one click away:

  1. Select a cell where you want to insert an age formula. To do this, visit the Ablebits Tools tab and then click the Date and Time group, and click the Date and Time Wizard button.
  2. The Date & Time Wizard will begin, and you'll be taken direct to age tab.
  3. On the Age Tab, there are three elements to indicate:
    • Birthdate as cell reference or date using the format mm/dd/yyyyyyy.
    • Age at the current day or particular date.
    • Choose whether to calculate age in months, days or years, or choose the precise age.
  4. Click the Insert formula button.

Done!

The formula is placed in the selected cell in a moment, and you double-click the fill handle to copy it down the column.

As you might have observed, the formula developed by the Excel age calculator will be more complicated than the formulas that we've previously discussed but it also accommodates the plural and singular of time units such as "day" and "days".

If you'd like to rid yourself of units that are zero like "0 days", select the Do not show zero units check box:
Calculate age ignoring zero units.

If you're curious to play with this age calculator as well as to find 60 other time-saving tools that can be added to Excel You're invited to download the trial Version of our Ultimate Suite. If you like the tools and want to purchase a license, make sure you don't miss this exclusive offer only for blog readers.

How to highlight certain particular ages (under or over a specific age)

In some instances, you may need not only calculate age in Excel and highlight cells that contain numbers that are either under or above a certain age.

If the age calculation formula yields the number of total years the formula can be used to design a regular conditional formatting rule that is based on a simple formula such as these:

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

C2 is the most top cell in the Age column (not including the column header).

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

Supposing the birthdates are in column B starting with row 2. The formulas are as follows:

  • 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 highlight the ages that are over 65 (blue): =DATEDIF($B2, TODAY (),"Y")>65

To create rules based on these formulas, simply select the rows, or the cells you'd like to highlight. Click the Home tab, then Styles and then select Conditionsal Formatting, New Rule... > Create a formula to determine which cells you should format.

The detailed steps are available in this article: how to make an automatic conditional formatting rule that is based on formula.

This is how you determine age with Excel. I hope that the formulas were simple for you to grasp and you will give them the chance to test them on 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

convert-to-jpeg

convert to bmp How to Create JPG to BMP for Free JPG format files can be very popular among people since all smartphones download images in JPG format but BMP format is not as common among the users as it is utilized in Windows computers. JPG as well as BMP as formats are excellent to store the files on computer computer but there is a difference between them. The majority of the time, JPG files have a small sizes but with low quality, while if you opt for BMP format to store your images , the quality of stored images will be better. If you're also struggling with problems with quality with your images, you can convert JPG to BMP using the aid of computer programs and online tools. In this article, we will provide five suggested JPEG conversion tools to Bitmap converters effortlessly in this article. If you're searching for a conversion from JPG BMP conversion online online UniConverter (Originally Media.io) ought to be your wish list NO.1. You'll need to ha...
Read more

Parts Per Million (ppm) Converter

Converter Parts Per Million (ppm) Parts Per Million (ppm) Converter for Gases This converter calculates the estimated amount (in the units [ppm[ppm]) Then it converts that number to those units of (mg/m 3. [mg/m 3] ) and reverses it. The unit ppm is employed in a variety of branches, in different ways. The use of ppm needs to be defined in the input fields below to ensure that it's converted into the appropriate unit. For more information about the use of ppm, go through the explanation below.In the input field for your Molecular Weight field, you can select from the drop-down menu or input your personal numbers to determine the molecular mass of gas. If the value of the molecular weight is not clear for you then take a look at our Molecular Weight Calculator.When you enter either or both or the the calculation in unit [ppm] within either the unit [mg/m 3 or [mg/m3The amount will be converted instantly and shown in the bottom section. The significance of th...
Read more

random number generator

Random Number Generator Use the generator to get an absolute randomly digitally secure number. It generates random numbers that can be utilized when accuracy of the results is essential for instance, when shuffling deck of cards to play the game of Poker as well as drawing numbers to win sweepstakes, giveaways or lottery. What is the most efficient way to select the best random number between two numbers? It is possible to use this random number generator for you to create a genuine random number from any two numbers. For instance, to get an random number that is in the range of one to 10 (including 10, you need to enter 1 first in the input field and 10 in the second field, then click "Get Random Number". The randomizer will select one from the numbers 1 through 10 randomly. To generate an random number between 1 and 100, follow the same procedure with 100, but it falls within the second field of the randomizer. To simulation of rolling dice, the number ...
Read more