age calculator

How do you 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 in the above image is an Excel Online sheet, so do not hesitate to input your birthdate within the appropriate cell, and you'll discover your age in a flash.

The calculator makes use of the following formulas to compute age using 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 can include an option to calculate age on a particular date as shown in the screenshot below:

To do this, you need to add the option buttons ( Developer tab > Insert > Form controls > Option Button) and then link them to some cell. And then, write an IF/DATEDIF-based formula to get age either at today's date or at the time specified by the user.

This formula follows 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", ""))

Also, put these functions together, and you'll be able to 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 in B10 and B11 operate with identical logic. Of course, they are far simpler since they use just one DATEDIF function to calculate age as the total of the months or days, or both.

To get the full details for the details, I recommend you take a look at the Excel Age Calculator and investigate the formulas found in cells B9:B11.

Download Age Calcqulator for Excel

Ready-to-use age calculator for Excel

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

  1. Select a cell into which you'd like to put in an age formula. Click on the Ablebits Tools tab, then the Date and Time group, and then click the Date and Time Wizard button.
  2. The Date & Time Wizard will begin, and you'll be taken directly to the page for age. tab.
  3. On the Age On the tab, you will find 3 things for you to specify:
    • Birthdate as a cell reference or a date using the format mm/dd/yyyyyyy.
    • Age at the present date or particular date.
    • Select whether to determine age in months, days and years or in exact age.
  4. Click the Insert formula button.

Done!

The formula is placed in the cell you have selected after which you double-click your fill button to duplicate the formula down the column.

You may have noticed, the formula created through the Excel age calculator Excel age calculator is more complex than those we've discussed so far however, it can be used for the plural and singular of time units, such as "day" and "days".

If you'd like rid of zero units , such as "0 days", select the Don't show zero units check box:
Calculate age ignoring zero units. src="https://cdn.ablebits.com/_img-blog/age-excel/age-without-zero-units.png"/>

If you're eager to see how you can use this age calculator as well as to find 60 other time-saving tools that can be added to Excel and Excel, we invite you to download a test Version of our Ultimate Suite. If you like the tools and you decide to purchase the license, don't overlook this special offer for our blog readers.

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

In certain circumstances you might not need to just calculate age in Excel but also highlight cells that have the ages that are less or over a particular age.

If your age calculation formula is able to calculate the total number of years, then you can create a conditional formatting rule based on a simple formula such as these:

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

Where C2 is the top-most cell in the Age column (not not including column head).

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

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

To create rules that are based on the formulas above, select those cells or entire rows you'd like to highlight. Go to the Home tab, then Styles section, and then click to create a new rule using Conditional Formatting... and then use a formula to determine the cells that you want to format.

The steps in detail are listed Here: The steps to make the conditional formatting rules built on formula.

This is how you calculate age within Excel. I hope the formulas were simple for you to grasp and that you'll give them an attempt in your worksheets. Thank you for taking the time to read and we look forward to seeing you here next week on our blog!

Comments

Post a Comment

Popular posts from this blog

What is the complete form of CRPF?

  What is the precise  form of CRPF ? About CRPF The  Central Reserve Police Force  was established as the Crown Representative's Police on 27th July 1939.   It was renamed an official part of the Central Reserve Police Force on the passing of the CRPF Act on 28th December 1949.   It has served for 82 years.   The Force has evolved into a huge organization having 246 Bns (including 203 executive Bns ) and five VIP Security Bns, 6 Mahila Bns, 15 RAF Bns, 10 CoBRA Bns aswell as the 05 Signal Bns (including 01 Special Duty Group, 01 Parliament Duty Group Special Duty Group, 01 Parliament Duty Group), 43 Group Centres , 22 Training Institutions 03 CWSand 07 AWS, 03 MWS/SWS, 04 Composite Hospitals with 100 beds 18 hospitals that are composite with 50 beds, and the 06 Field Hospitals. Mission The purpose of the Central Reserve Police Force Central Reserve Police Force will be to assist the government in its mission to guarantee Rule of Law, Public Order and ...
Read more

Random Number Generator

Random Number Generator: How Do Computers Generate Random Numbers? People have been playing with random number generators for millennia. So the idea isn't a new idea. From the lottery of the ancient city of Babylon to the roulette table in Monte Carlo, to dice games in Vegas The goal is leaving the final outcome the hands of random chance. However, aside from gambling, randomness has numerous applications in the fields of science, statistics, cryptography, and much more. However, using coins, dice or other similar media to serve as an random device is not without its drawbacks. Due to their mechanical character of methods, generating large quantities of random numbers takes a an enormous amount of time and effort. Human ingenuity is the reason why we have more efficient techniques and tools available to us. Methods for generating random numbers True Random Numbers Image of an analog-input digital output processing device. Photo by Harrison Broadbent L...
Read more