age calculator

How can I create an age calculator in 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 here is an embedded Excel Online sheet, so do not hesitate to input your birthdate in the corresponding cell, and you will be able to determine your age in just a few seconds.

The calculator makes use of the following formulas to compute age in relation to the web page's date of birth in cell A3 as well as the date of today.

  • 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 may add an option that allows you to calculate age on a particular date, like shown in the following picture:

In order to do this, add the option buttons ( Developer tab > Insert > Form controls > Option Button) and then link them to some cell. Also, create an IF/DATEDIF calculation to determine age in accordance with the current date or on the date indicated by the user.

The 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", ""))

Finally, nest the above functions together, then you'll 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 similar logic. Of course, they are much simpler because they include just one DATEDIF function to calculate age as the number of full months or days.

To find out more To find out more, download the Excel Age Calculator and investigate the formulas that are found in 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 the age calculator in Excel - it's only a couple of clicks away:

  1. Select a cell where you would like to add an age formula. To do this, visit the Ablebits Tools tab, then the Date & Time group, then click the Date & Time Wizard button.
  2. When you click on the Date & Time Wizard will begin, and you'll be taken right to the aged tab.
  3. On the Age tab, there are 3 options to choose from:
    • Birthdate as an individual cell reference or date using the format mm/dd/yyyyyyy.
    • Age at the current the date or particular date.
    • Decide whether to determine age in months, days, years, or precise age.
  4. Click the Insert formula button.

Done!

The formula is added to the cell that you are currently in, and you double-click onto the handle for fill to paste it down the column.

As you may have noticed, the formula created from our Excel age calculator will be much more complex than the one we've been discussing however, it can be used for the plural and singular of time units such as "day" and "days".

If you'd like to rid yourself of zero units , such as "0 days", select the Do not show zero units checkbox:
Calculate age ignoring zero units.

If you're eager to test the age calculator as well as to discover 60 more time-saving tools that can be added to Excel You are invited to download a free trial edition of the Ultimate Suite. If you're impressed with the tools and choose to purchase a license, don't miss this exclusive offer only for blog readers.

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

In some instances, you may need not only determine age in Excel but also highlight cells that have aged numbers that are lower or over a particular age.

When your age calculation formula returns the number of complete years it is possible to design a regular conditional formatting rule built on a basic formula, like the following:

  • To show ages that are equal or greater than 18:
  • To highlight ages under 18: =$C2<18

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

But what happens if the formula is displaying age in months and years or in years months and days? In this case, you will have create a rule built on a 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 draw attention to age groups beyond 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, click the Home tab, then Styles and then select Conditionsal Formatting, New Rule... and then use a formula to identify which cells to format.

The specific steps are available Here: The steps to make a conditional formatting rule built on formula.

This is how you calculate age by using Excel. I hope the formulas are simple for you to grasp and that you'll 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

power-converter

Converter Parts Per Million (ppm) Parts Per Million (ppm) Converter for Gases This converter calculates an number of ppm calculated (in increments in units of [ppmin]) in order to convert it into the equivalent measurement unit [mg/m 3. [mg/m 3] ] and vice versa , and reverse. The unit ppm could be utilized in various branches in different ways. The usage of ppm needs to be specified in the fields for input below, and how it can be converted into the appropriate unit. For more information about the use of ppm, check out the definition below.In the input fields for the Molecular Weight field, you can select the drop-down or enter your own number into the field for molecular weight of the gas. If the molecular weight of the gas is not something you know, you should use our Molecular Weight Calculator.When you input either or both number or you enter the formula in units of ppm or with units like (mg/m3) 3 or [mg/m3 The quantity will be converted in a flash and then dis...
Read more

Parts Per Million (ppm) Converter

Converter Parts Per Million (ppm) Parts per Million (ppm) Converter for gases. This converter calculates how much the amount of the gas's volume measured in ppm units. It then transforms it into the equivalent units of mg/m 3] and in reverse. The unit ppm is utilized in a myriad of branches in various ways. The usage of ppm must be defined in the fields that are below to be inputs, to ensure that it is converted to the proper unit. For more information about the application of ppm look at the manual below.In the field labeled "Molecular Weight, you will find the entry field for this field. Molecular Weight can be selected by selecting the drop-down menu. Or put in the molecular mass within of the gaz. If the molecular weight is not identified for you, try our molecular weight Calculator.When you enter either of the as well as the n formula in units of [ppm] or units of [mg/min units of [mg/m 3]. The value is converted instantly and displayed in the low...
Read more

convert-to-jpeg

convert to BMP How to Create JPG to BMP for Free JPG file formats are extremely popular among people since smartphones open images in JPG format, however the BMP format is not much popular among users since it is used by Windows computers. JPG and BMP all formats are great for storing files on computers. computer but there are some differences between them. It is common for JPG images are of a small sizes, however they are of poor quality, however if you utilize BMP format for storing images, the quality of your images will be superior. If you're also experiencing the issue of low quality photos, then you could change JPG images to BMP by making use of desktop and online programs. Here , we'll show you five recommended JPEG convertors to Bitmap converters easily in this article If you're seeking a JPG convertor to BMP converter that is online online, then Online UniConverter (Originally Media.io) ought to be on your list NO.1. You'll need to have a quest...
Read more