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

What you see here is an embedded Excel Online sheet, so you can enter your birthdate in the corresponding cell, and then you'll get your age within a matter of seconds.

Calculators use the formulas listed below to calculate age in relation to the web page's 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 working with Excel Form controls, you can include an option to compute age at a specific date as illustrated in the following picture:

In order to do this, add two option buttons ( Developer tab > Insert > Form controls > Option Button) and connect them to a cell. After that, you write an IF/DATEDIF equation to calculate age as of today's 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", ""))

In the end, combine the above functions in a way, and 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 in B10 and B11 work with exactly the same formula. Of course, they're considerably simpler, as they both contain just one DATEDIF function to calculate age as the total number of days or days.

To understand the specifics I encourage you to download the Excel Age Calculator and investigate the formulas within cells B9:B11.

Download Age Calcqulator for Excel

Useful and ready-to-use age calculator for Excel

Our users of the Ultimate Suite don't have to bother about making your own age calculator in Excel - it is only few clicks away:

  1. Select a cell where you'd like to put in an age formula. Click on the Ablebits Tools tab and then click the Date & Time group, then click the Date & Time Wizard button.
  2. It will begin the Date & Time Wizard will begin and then you go direct to aged tab.
  3. On the Age On the tab, you will find 3 items to be specified:
    • Birth date data as a cell reference or a date in the mm/dd/yyyy format.
    • Age at today's date or an exact date.
    • Choose whether to calculate age in months, days, years, or exactly age.
  4. Click the Insert formula button.

Done!

The formula is added to the selected cell momentarily after which you double-click in the Fill handle, to transfer it into the column.

You may have observed, the formula created in the Excel age calculator has a formula that is more complicated than the other formulas we've been discussing, but it caters for the plural and singular of time units like "day" and "days".

If you'd like to rid yourself of units that are zero like "0 days", select the Don't display zero units checkbox:
Calculate age ignoring zero units.

If you're interested to check out this age calculator as well as to learn about 60 additional time-saving tools that can be added to Excel, you are welcome to download a trial edition of the Ultimate Suite. If you like the tools and decide to get an account, don't forget to take advantage of this special offer for our blog readers.

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

In certain situations you might not need to simply calculate age in Excel, but also highlight cells with age ranges that are below or over a particular age.

If you find that the age calculation formula returns the number of complete years and you want to create a conditional formatting rule using a formula like these ones:

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

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

What happens if your formula will display age in years and months, or in years, months and days? In this case, you will have to create a rule which is based on the DATEDIF formula which calculates age from date of birth in years.

Supposing the birthdates are in column B, beginning with row 2, the formulas are:

  • 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 the ages beyond 65 (blue): =DATEDIF($B2 (TODAY (),"Y")>65

To make rules based on the above formulas, select the cells or entire rows which you would like to highlight. Go to the Home tab, then Styles, then select conditional formatting > Create Rule... > Use a formula to determine which cells to format.

The complete steps can be found Here: The steps to make the conditional formatting rule, basing on a formula.

This is the method you use to calculate age within Excel. I hope the formulas are easy to understand and that you give them a a try in your worksheets. Thank you for reading , and I hope to see you back here next week on our blog!

Comments

Post a Comment

Popular posts from this blog

Parts Per Million (ppm) Converter

Converter Parts Per Million (ppm) Parts per Million (ppm) Converter for gases The converter calculates the determined amount (in units ) of [ppm(in unit of [ppm) in order to convert it into one of the unit (mg/m 3. [mg/m 3] and vice versa. The unit ppm is used throughout the various branches, in various ways. The use of ppm has been specified in the fields to input below, so that it could be converted to the correct unit. For more information on the use of ppm, go through this explanation below.In the fields for input for Molecular Weight, you can select the drop-down option or type in your own number in the field to indicate the molecular weight for the gas. If the molecular mass isn't known for you, try our Molecular Weight Calculator.When you enter either the formula or n the formula in units of [ppm] or the units [mg/m 3 or [mg/m 3The value will be instantly converted and will be displayed in the lower part of the screen. The significance is automatically determine...
Read more

power-converter

Converter Parts Per Million (ppm) Parts per million (ppm) Converter for Gases This converter calculates an estimated quantity (in units that is [ppm]) and converts it into those units (mg/m 3. [mg/m 3] ) and reverse. The unit ppm has been used in many branches, in a variety of ways. The application of ppm has been clearly defined in the input fields below, as well as the method by which it is able to be converted into the appropriate unit. For more details on the use of ppm, take a look at the clarification below.In the relevant field you can enter into the Molecular weight field. you can select the drop-down menu or type in the value of your choice that corresponds to the molecular mass of gas. If the concept of molecular weight doesn't seem clear to you, then we suggest you try our Molecular Weight Calculator.When you enter either or or the calculation in unit [ppm] in the values (mg/m 3 or [mg/m 3] The quantity will be converted immediately and is displayed in the...
Read more

random number generator

TEMPLATE DESCRIPTION It's amusing to know how much time have you spent on earth. Also, do you know how many minutes or days have passed since you got married? Just check with your Family Age Calculator in Excel to find out! This Excel template can be used as a basic calculator for your family members. It is constructed by two sections: Dashboard Age Counter Dashboard It comes with appealing design and a simple dashboard. It will allow you to input the name of your family members, their birthday and it can also be used to show how long have passed since the parents wedding. Age Counter: Here the template will calculate different things regarding the age you input. How many days, months, Hours, Minutes and Minutes did resided! The calculations will be based on to the current date in your system using TODAY() function in Microsoft Excel. Print your own copy of this Excel spreadsheet template, and export it to PDF as like. Are you a famil...
Read more