age calculator

How to make 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 take the time to enter your birth date in the appropriate cell, and you will discover your age in just a few seconds.

The calculator utilizes the formulas listed below to calculate age according to the web page's 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've had some experience using Excel Form controls, you may add an option that allows you to compute age in a specified date such as in the following picture:

For this, add two options buttons ( Developer tab > Insert > Form controls > Option Button), and link them to a cell. Also, create an IF/DATEDIF equation to calculate age in accordance with the current date or at the time specified 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", ""))

Then, you can nest the above functions within each other and you will 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 are based on exactly the same formula. Of course, they are far simpler since they use only one DATEDIF function to calculate age as the number of complete months or days, respectively.

To get the full details For more information, I suggest you Download this Excel Age Calculator and investigate the formulas used in cells B9 and B11.

Download Age Calcqulator for Excel

Useful and ready-to-use age calculator for Excel

The users of our Ultimate Suite don't have to make your own age calculator in Excel - it's only one click away:

  1. Choose a cell in which you would like to add an age formula, go to the Ablebits Tools tab and then click the Date and Time group, and click the Date & Time Wizard button.
  2. When you click on the Date & Time Wizard will begin and then you go directly to the age tab.
  3. On the Age tab, there are 3 things to mention:
    • Birth date data as cell reference or date using the format mm/dd/yyyyyyy.
    • Age at today's date or particular date.
    • Choose whether to calculate age in months, days year, or even precise age.
  4. Click the Insert formula button.

Done!

The formula is added to the cell that you are currently in after which you double-click the fill handle to copy it down the column.

You may have observed, the formula developed in Excel's Excel age calculator will be more complex than the ones we've covered so far but it also accommodates singular and plural of time units like "day" and "days".

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

If you're interested to try this age calculator as well as to find 60 other time-saving tools that can be added to Excel, you are welcome to download a free trial Version of our Ultimate Suite. If you like the tools and choose to purchase the license, don't overlook this great deal exclusively for blog readers.

How do I highlight certain types of ages (under or over a specific age)

In some instances you might not need to just to calculate age in Excel, but also highlight cells that contain age ranges that are below or over a specific age.

If the age calculation formula yields the number of complete years it is possible to design a common conditional formatting rule using a formula such as these:

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

C2 is the most top cell of the column called Age (not not including column head).

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

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

To create rules using the above formulas, select the rows, or the cells which you would like to highlight, click the Home tab, then Styles group, and click Conditionsal Formatting, New Rule... > Create an equation to decide which cells to format.

The steps in detail can be found Here: The steps to create an underlying conditional formatting rule that is based on formula.

This is how you determine age by using Excel. I hope the formulas were easy for you to master and that you give them a the chance to test them on your worksheets. Thank you for reading , and we look forward to seeing you on our blog next week!

Comments

Post a Comment

Popular posts from this blog

LENGTH CONVERTER

  LENGTH CONVERTER Choose the length you'd like to use, and then enter the amount you'd like to convert to a different measurement of length in the "From" field, and choose the measurement you'd like to convert it into within the "To" field.   The result of the conversion will be visible within the "To" field right after you click on any unit. It is the term"length," which you've likely heard of , is defined in the context of "length" as a precise  measurement  of anything from one end to the next.   It's difficult to locate exact measurements that convert miles into kilometers.   Sometimes, we require an estimate of the time it will take others to get there or the amount of time it takes to reach there, and that's the reason length converters are useful in calculations.   Measurement  Converter is a tool that can be used according to the requirements since it is able to convert any length, and will assist you in...
Read more

roadies-audition-form-pdf

Roadies Audition Form PDF | Roadies 2021 Audition Dates and Venues Use the link below in order to obtain the Roadies Audition Form PDF . Roadies Audition Form You can sign up for MTV Roadies Revolution 2020 by visiting the official website or the show's website. You can sign up by clicking on the button below. You will find the URL to our registration page in this web page. The Roadies Audition Form can be downloaded on the internet 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 Some television Shows accept auditions because of being affected by the Covid 19 Pandemic. The majority of TV Auditions take place in Mumbai and Mumbai, as we are aware. CIDIV Second Wave is coming to Maharashtra , and there are plenty of instances. MTV will conduct the Roadi...
Read more

convert-to-jpeg

convert to BMP How to Create JPG to BMP for Free JPG formats are widespread among the population as phones view pictures in JPG format but BMP format isn't as common among the users as it is used for windows computers. JPG in addition to BMP each formats are suitable to save files onto your computer but you will notice that there are some differences between them. Usually, JPG files have a small dimensions, but they're of poor quality, while if you opt for BMP format for storing your images then the quality of images saved will be higher quality. If you're experiencing issues with low-quality with your images, you can convert JPG files to BMP by making use of desktop and online tools. Here we will show you 5 suggested JPEG in Bitmap converters without difficulty in this post. If you're in search of a the JPG-to BMP converter that is online , online UniConverter (Originally Media.io) ought to be on your list of options NO.1. You'll need to have the que...
Read more