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 don't hesitate to enter your birthdate in the relevant cell, and you'll be able to determine your age in a moment.

The calculator uses the formulas listed below to calculate age based on the formulas below based on the date of birth in cell A3 and 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 have some experience using Excel Form controls, you could add an option to calculate age at a specific date as illustrated in the following screenshot:

For this, insert a couple of option buttons ( Developer tab > Insert > Form controls > Option Button) and then link them to a cell. And then, write an IF/DATEDIF-based formula to get age as of today's date or at the time specified by the user.

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

Last but not least, put the above functions into each other, and you'll have the complete age calculation formula (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 of B10 and B11 are based on the same logic. Of course, they're more straightforward because they contain only one DATEDIF function to return age as the number of full months or days.

To learn the details To find out more, install this Excel Age Calculator and investigate the formulas used in cells B9 and B11.

Download Age Calcqulator for Excel

Ready-to-use age calculator for Excel

The users of our Ultimate Suite don't have to create themselves an age calculator in Excel - it is only a few clicks away:

  1. Select a cell that you would like to add an age formula. Click on the Ablebits Tools tab, then the Date & Time group, and then click the Date & Time Wizard button.
  2. It will begin the Date & Time Wizard will begin, and you will be able to go straight to the age tab.
  3. On the Age Tab, there are 3 elements to indicate:
    • Birth date data as a cell reference or a date using the format mm/dd/yyyyyyy.
    • Age at the present the date or an exact date.
    • Choose whether you want to calculate age in terms of days, months year, or even absolute age.
  4. Click the Add formula button.

Done!

The formula is then inserted into the cell you have selected before you click the fill handle to copy it into the column.

As you've probably observed, the formula created from our Excel age calculator is more complex than the ones 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 like to dispose of zero units such as "0 days", select the Don't show zero units check box:
Calculate age ignoring zero units.

If you're curious to see how you can use this age calculator as well as for more ways to save time with 60 other tools for Excel and Excel, we invite you to download a free trial edition of the Ultimate Suite. If you're impressed and decide to get an account, don't forget to take advantage of this deal for our blog readers.

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

In certain instances there may be a need to not just calculate age in Excel and highlight cells which contain age ranges that are below or over a particular age.

If your age calculation formula returns the total number of years that you have, you can design an ordinary conditional formatting rule that is based on a simple formula, like the following:

  • To emphasize ages equal to or higher than 18:
  • To highlight ages under 18: =$C2<18

C2 is the most top cell of the column called Age (not counting the header of the column).

What if your equation is displaying age in months and years and days, or even in years days and months? In this situation, you will have to design a rule that is built on a DATEDIF formula which calculates age from date of birth in years.

If you assume that 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)
  • To emphasize age groups that are over 65 (blue): =DATEDIF($B2, TODAY (),"Y")>65

To make rules based on the above formulas, select those cells or entire rows that you want to highlight. Click the Home tab, then Styles group, and select conditional formatting > Create Rule... > Use a formula to identify the cells that you want to format.

The detailed steps are listed on this page: How to make the conditional formatting rule, that is based on formula.

This is the method you use to determine age using Excel. I hope the formulas are easy to understand and that you give them a an attempt in your worksheets. Thank you for taking the time to read and hope to see you 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 GasesThis converter calculates an calculated quantity (in the increments of units [ppmin] of [ppm) to convert it into the equivalent measurement unit [mg/m 3. [mg/m 3] ] or vice versa and in reverse. The unit ppm can be used in different branches in a variety of ways. The usage of ppm needs to be described in the input fields below and the way it can be converted into the proper unit. For more information on the usage of ppm, check out the definition below.In the input fields on the molecular weight field, you can choose the drop-down option or enter your own number in the field for molecular mass of gas. If the molecular weight of the gas is unknowable to you, then we recommend using our molecular weight Calculator.When you input either or both the calculations in units of ppm or with units like (mg/m3) 3 or [mg/m3The quantity will be converted immediately and will be shown in the fi...
Read more

LENGTH CONVERTER

  LENGTH CONVERTER Choose the length you want to use, and then type in the amount that you'd like to change into a different length measurement within the "From" field, and select the unit you'd prefer 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. "length" or the term"length," which you've probably heard of, is described by the term "length" as a precise  measurement  of anything from one end to the other.   It's difficult to locate precise measurements for converting miles into kilometers.   Sometimes, we require an estimate of the time it will take others to travel to the destination or the amount of time it takes to reach there, and that's why length conversion tools can prove useful in calculations.   Measurement  Converter can be used in accordance with the conditions as it can convert any length type. I...
Read more

What is the complete form of CRPF?

  What is the full  form of CRPF ? About CRPF The  Central Reserve Police Force  was created as Crown Representative's Police on 27th July 1939.   It was transformed into the Central Reserve Police Force on the promulgation of the CRPF Act on 28th December 1949.   It has been in existence for more than 82 glorious years.   The Force has grown into a big organization with 246 Bns (including 203 executive Bns,05 VIP Security Bns and 6 Mahila Bns, 15 RAF Bns, 10 CoBRA Bns, 05 Signal Bns and 01 Special Duty Group, 01 Parliament Duty Group Special Duty Group, 01 Parliament Duty Group), 43 Group Centres, 22 Training Institutions, 03 CWS, 07 AWS 03 MWS/SWS Hospitals with 100 beds 18 composite hospitals of 50 beds and 06 Field hospitals. Mission The role of Central Reserve Police Force Central Reserve Police Force shall help the government to maintain Rule of Law, Public Order and Internal Security efficiently and effectively as well as to ensure the protectio...
Read more