age calculator

How to create an age calculator in Excel? 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 below is an embed Excel Online sheet, so do not hesitate to input your birthdate within the appropriate cell and you'll find your age in a moment.

Calculators use these formulas to calculate age in relation 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 have some experience working with Excel Form controls, you can add an option to compute age at a certain date, like shown in the image below:

To accomplish this, add two options buttons ( Developer tab > Insert > Form controls > Option Button) Add them to some cell. Also, create an IF/DATEDIF equation to calculate age that is at present or the date set by the user.

The formula is based on 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 will get the entire 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 use identical logic. Of course, they are more straightforward because they contain only one DATEDIF function that returns age as the total of the months or days, respectively.

For more information for the details, I recommend you Download 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

Users of our Ultimate Suite don't have to think about creating their own age calculator in Excel - it's just few clicks away:

  1. Select a cell where you would like to add an age formula. Then, click the Ablebits Tools tab > Date & Time group, and click the Date & Time Wizard button.
  2. The Date & Time Wizard will begin, and you'll be taken straight to the aged tab.
  3. On the Age tab, there are 3 options to choose from:
    • Data of birth as cell reference or date in the format mm/dd/yyyyyy.
    • Age at today's the date or specific date.
    • Choose to calculate age in months, days and years or in absolute age.
  4. Click the Formula to insert button.

Done!

The formula is added to the selected cell in a moment when you double-click on in the Fill handle, to transfer it to the column.

As you've probably noticed, the formula created in the Excel age calculator is more complicated than the formulas we've covered so far but it also accommodates singular and plural of time units such as "day" and "days".

If you'd like to dispose of zero units , such as "0 days", select the Don't show zero units check box:
Calculate age ignoring zero units.

If you're eager to play with the age calculator as well as to find 60 other time-saving add-ins to Excel You're invited to download a trial edition of the Ultimate Suite. If you're impressed with the tools and decide to get an upgrade, don't be averse to this offer exclusively for our blog readers.

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

In some situations, you may need not only determine age in Excel but also highlight cells that have age ranges that are below or above a certain age.

If your age calculation formula results in the number of full years it is possible to design an ordinary conditional formatting rule based on a simple formula such as these:

  • To indicate ages equivalent to or greater than 18: =$C2>=18
  • To highlight ages under 18: =$C2<18

C2 is the highest cell in the column titled Age (not comprising the header column).

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

If the birthdates occur in column B and begin 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 show age groups that are over 65 (blue): =DATEDIF($B2, TODAY (),"Y")>65

To create rules that are based on the formulas above, select the rows, or the cells that you want to highlight. To do this, click the Home tab > Styles section, and then click Conditional Formatting > New Rule... > Use a formula to determine the cells that you want to format.

The specific steps are available below: Steps to make an automatic conditional formatting rule that is based on formula.

This is the method you use to determine age with Excel. I hope that the formulas were easy for you to learn and you will give them a try in your worksheets. Thank you for reading , and I hope to see you back in our next blog post!

Comments

Post a Comment

Popular posts from this blog

BMI Calculator

BMI Calculator It is this calculator that is Body Mass Index Calculator ( BMI calculator ) is an important tool that will help you assess the body's weight. With the help of the calculator, you can determine if you're an ideal weight. This can reduce the chance of developing obesity-related illnesses, including high blood pressure as well as the heart health issues of type 2 diabetes and many other. What Is BMI (Body Mass Index)? BMI is an indicator instrument that can tell you whether it is overweight, overweight normal-weight or underweight at your weight. BMI evaluates your body mass to determine if your body mass falls to the healthy weight range. In addition to measurement of your height, BMI also depends on the age of your body and gender. You can make use of the internet-based BMI calculator to help you understand the significance of. BMI is a measurement of an individual's leanness or corpulence based on their weight and height, and is designed to ga...
Read more

convert the image to png

convert to BMP Do you wish to convert an Images of one type to another? Like PNG to JPG or vice versa? Or maybe even change an image to PDF? It's either for performance, quality, or any other reason there are many situations where you're required alter the format of an image. In this article, we'll share six great conversion image tools that let you swiftly modify any image for one file format another format (or even bulk convert images if needed). The best six tools to convert image into a different format In the wake of our discussion on why it might be beneficial to convert images to an image form, lets look at the top six options. They're all pretty similar capabilities, so the one you choose that is the most appropriate for you will depend on the amount of images that you want to convert regularly. Convertio CloudConvert PhotoBulk Tinybeest Online Convert Convertio ...
Read more