age calculator

How to make an age calculator in Excel? 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 in the above image is an Excel Online sheet, so feel free to enter your birthdate in the corresponding cell and you'll be able to determine your age in just a second.

The calculator utilizes the following formulas to compute age according to the formulas below based on the date of birth in cell A3 and 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 working with Excel Form controls, you may add an option that allows you to compute age in a specified date like in the following image:

To do this, you need to add a couple of option buttons ( Developer tab > Insert > Form controls > Option Button) Add them to some cell. Then, you can write an IF/DATEDIF calculation to determine age either at today's date or the date set by the user.

The formula operates according to 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", ""))

In the end, combine the above functions into each other, then you'll get the complete 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 found in B10 and B11 operate with the same logic. Of course, they are considerably simpler, as they both contain only one DATEDIF function that returns age as the number of full months or days, respectively.

For more information 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

Our users of the Ultimate Suite don't have to make the age calculator in Excel - it is only a couple of clicks away:

  1. Select a cell to which you want to insert an age formula. Then, click the Ablebits Tools tab and then click the Date & Time group, and click the Date & Time Wizard button.
  2. It will begin the Date & Time Wizard will startand then you can go directly to the age tab.
  3. On the Age tab, there are 3 things to mention:
    • Birthdate as a cell reference or a date in the format of mm/dd/yyyyyy.
    • Age at the current day or specific date.
    • Choose to determine age in terms of days, months year, or even precise age.
  4. Click the Insert formula button.

Done!

The formula will be inserted into the selected cell momentarily before you click the fill handle to copy it to the column.

As you may have seen, the formula generated through Excel's Excel age calculator Excel age calculator is more complex than those we've talked about so far however, it can be used for the singular and plural of time units, such as "day" and "days".

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

If you're interested to try the age calculator as well as to discover 60 more time-saving tools for Excel and Excel, we invite you to download the trial edition of the Ultimate Suite. If you're satisfied with the tool and want to purchase a license, don't miss this exclusive offer only for blog readers.

How do you highlight specific ages (under or over a certain age)

In some situations you might need to only calculate age in Excel but also highlight cells that have numbers that are either under or over a specific age.

When your age calculation formula gives you the number of total years it is possible to design an ordinary conditional formatting rule built on a basic formula, like the following:

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

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

But what if your formula shows age in years and months, or in years, days and months? In this instance you'll need to create a rule 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:

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

To create rules based on the above formulas, select the cells or entire rows which you would like to highlight. Click the Home tab, then Styles group, and select Conditional Formatting > New Rule... > Apply a formula to determine the cells that you want to format.

The steps in detail are listed on this page: How to create the conditional formatting rule, built on formula.

This is how you calculate age in Excel. I hope that the formulas were easy for you to master and that you give them a a try in your worksheets. Thank you for reading and we hope to see you again in our next blog post!

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 what the quantity of gas's volume as measured in ppm units and converts it into unit [mg/m 3[mg/m 3] and in reverse. The unit ppm can be used in many branches in a variety of ways. The use of ppm should be specified in the fields listed below to input, to ensure that it is transformed into the correct unit. For more details on the use of ppm, take a look at the guide below.In the field called "Molecular Weight, you will see the entry field where that field. Molecular Weight can be selected from the drop-down menu , or input the molecular weight in of the gaz. In case the weight of the molecules isn't well-known to you, then check out our molecular weight Calculator.When you input the formula or one of the and the formulas, either the formula in units of [ppm] or units of [mg/m3] or [mg/m 3[mg/m] 3. The value is converted instantly and displayed i...
Read more

Calorie Calculator

Calorie Calculator The Calorie Calculator can be used to calculate the number of calories an individual needs to consume each day. This calculator will also give fundamental guidelines for either gaining or losing weight. Food Energy Converter This converter could be utilized to convert Calories as well as other units used for energy consumption in food. Related:BMI Calculator | Body Fat Calculator | Ideal Weight Calculator That Calorie Calculator has been built on a variety of equations, and the calculations of the calculator are based on an approximate average. That Harris-Benedict Equation was one of the first equations to be utilized to calculate the basal metabolic rate (BMR), which corresponds to the amount of energy which is used up daily in a resting state. The equation was revised in 1984 to become more accurate and was used through 1990, at which it was at that point that when the Mifflin-St Jeor Equation was introduced. It is the Mifflin Saint Jeor Equa...
Read more

power-converter

Converter Parts Per Million (ppm) Parts Per million (ppm) Converter for GasesThis converter calculates what is the estimated amount (in increments of [ppmin units of) in order to convert it into an equivalent unit of measurement [mg/m 3. [mg/m 3] ] and vice versa and reverse. The unit ppm could be used in different branches in various ways. The use of ppm has to be described in the input fields below and in the manner it can be converted into the proper unit. For more information about the meaning of ppm examine the definition below.In the fields for input in the field for Molecular Weight, you can select the drop-down box or enter your own number in the field for molecular-weight of the gas. If the molecular weight of the gas is not something you know, we recommend using our molecular weight Calculator.When you enter either or both or you enter the formula in units of ppm or in units of (mg/m3) 3 or [mg/m3] the amount is instantly converted and then displayed in the...
Read more