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

The image above , is an embedded Excel Online sheet, so take the time to enter your birthdate in the corresponding cell and you'll get your age within a matter of seconds.

The calculator uses the following formulas to calculate age based on the "" date of birth in cell A3 and the date of birth in cell A3 and 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 with Excel Form controls, you can include an option to calculate age at a specific date such as in the following screenshot:

To accomplish this, add the option buttons ( Developer tab > Insert > Form controls > Option Button) and connect them to some cell. And then, write an IF/DATEDIF calculation to determine age in accordance with the current date or on the date indicated by the user.

The formula works with 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 will 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 use exactly the same formula. Of course, they're far simpler since they use just one DATEDIF function to calculate age as the number of complete months or days, or both.

For more information To find out more, Download this 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 bother about making an own age calculator in Excel - it is only a couple of clicks away:

  1. Select a cell into which you'd like to put in an age formula. Then, click the Ablebits Tools tab > Date & Time group, and then click the Date & Time Wizard button.
  2. The Date & Time Wizard will start, and you go immediately to the tab for Age. tab.
  3. On the Age Tab, there are three things to mention:
    • Birth date data as cell reference or date in the format of mm/dd/yyyyyy.
    • Age at the current day or an exact date.
    • Decide 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 before you click your fill button to duplicate it into the column.

As you might have noticed, the formula formulated from Excel's Excel age calculator will be much more complex than the one we've talked about so far but it does take into account singular and plural of time units like "day" and "days".

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

If you're interested to play with the age calculator as well as to discover more time-saving tools that can be added to Excel and Excel, we invite you to download the trial version of our Ultimate Suite. If you're impressed and you decide to purchase an account, don't forget to take advantage of this great deal exclusively for blog readers.

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

In some situations you might need to only determine age in Excel but also highlight cells with the ages that are less or over a particular age.

If your age calculation formula yields the number of full years, then you can create a regular conditional formatting rule built on a basic formula similar to these:

  • To show ages that are equal or more 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).

But what happens if the formula has age in years and months or in years days and months? In this case, you will have to develop a rule that is based on a DATEDIF formula which calculates age from date of birth in years.

If the birthdates are located in column B, beginning 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 above 65 (blue): =DATEDIF($B2, TODAY (),"Y")>65

To create rules that are based on the formulas described above, simply select the rows, or the cells that you want to highlight. To do this, click the Home tab > Styles, then select the New Rule button... > Apply a formula to determine the cells that you want to format.

The specific steps are listed below: Steps to create a conditional formatting rule built on formula.

This is how you determine age by using Excel. I hope the formulas are 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

roadies-audition-form-pdf

Roadies Audition Form PDF | Roadies 2021 Audition Dates and Venues Click the link below for downloading this PDF Roadies Audition Form PDF . Roadies Audition Form You can register for the Roadies Revolution 2020 on MTV. Roadies Revolution 2020 by going to the official website or on the show. You can sign up by clicking the link below. You will find the link to the registration page at the bottom of this webpage. Roadies Audition Form Roadies Audition Form can be downloaded online in PDF format , by clicking the link below. Date and Venue MTV India Roadies Audition 2021 Rev X9 Registration Online for Season 19 Auditions Date, Venue, Eligibility Criteria and Documents Needed, Terms and Conditions, Selection Process Not all TV Shows will accept auditions because of an issue with the Covid 19 Pandemic. The majority of TV Auditions take place in Mumbai and Mumbai, as we are aware of. The CIDIV 2nd Wave has been Coming to Maharashtra , and there ar...
Read more

Random Number Generator

Random Number Generator: How Do Computers Generate Random Numbers? People have been using random number generators for thousands of years, so the idea isn't a new idea. From the lottery system in the ancient city of Babylon and roulette games in Monte Carlo, to dice games in Vegas the idea was to make the final outcome for random chance. While gambling is not the only option, randomness has numerous applications in the fields of scientific research, statistics, cryptography, and much more. But using dice, coins or similar materials for an random device has its limitations. Due to its mechanical basis for these methods, generating large quantities of random numbers requires an enormous amount of time and effort. With the help of human innovation, we've more powerful equipment and methods at our disposal. Methods for generating random numbers True Random Numbers Image of digital input analog output processing device. Photo by Harrison Broadbent Let...
Read more

Scientific Calculator

Best Scientific Calculator for Engineering & Architecture Students in 2021 With the increase in the number of college students and professionals in and around the Engineering and Information Technology sector The need of scientific calculators is growing. The top scientific calculators for engineering students are nothing but electronic calculators, which are usually hand-held, and are designed to tackle the more difficult issues of science and mathematics. QUICK COMPARISON: Our Top 3 Picks For Scientific Calculators There are various versions that make up different versions of the science calculator and every Engineering or science student would relate to the usage and the necessity of scientific calculators in their daily everyday. Best Scientific Calculator for Engineering Students To select the most effective scientific calculator isn't easy with the huge variety of brands and calculators that are on the market, a short review would be useful. ...
Read more