age calculator

How can I create 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 above , is an embedded Excel Online sheet, so you can enter your birthdate in the relevant cell, and you'll get your age in just a second.

Calculators use the following formulas to compute age based on the "" date of birth in cell A3 and 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've got some experience using Excel Form controls, you can include an option to calculate age at a certain date like in the following picture:

To do this, you need to add two option buttons ( Developer tab > Insert > Form controls > Option Button) and then link them to some cell. After that, you write an IF/DATEDIF-based formula to get age or at the time of today's date or at the date specified by the user.

The formula is based on 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 into each other, 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 used in B10 and B11 follow an identical logic. Of course, they're much simpler because they include just one DATEDIF function to calculate age as the total of the months or days, respectively.

To get the full details I encourage you to download the Excel Age Calculator and investigate the formulas that are found in cells B9:B11.

Download Age Calcqulator for Excel

Useful and ready-to-use age calculator for Excel

Users of our Ultimate Suite don't have to make their own age calculator in Excel - it is only a few clicks away:

  1. Choose a cell in which you'd like to place an age formula. To do this, visit 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 immediately to the aged tab.
  3. On the Age On the tab, you will find 3 elements to indicate:
    • Birth date data as an individual cell reference or date in the format of mm/dd/yyyyyy.
    • Age at the current day or particular date.
    • Choose whether you want to determine age in days, months and years or in exact age.
  4. Click the Add formula button.

Done!

The formula is placed in the cell you have selected when you double-click on in the Fill handle, to transfer it down the column.

As you've probably observed, the formula developed from our Excel age calculator can be more complicated than the other formulas we've covered so far but it also accommodates the plural and singular of time units like "day" and "days".

If you'd like to rid yourself of zero units like "0 days", select the do not show zero units check box:
Calculate age ignoring zero units.

If you are curious to try this age calculator as well as to discover 60 more time-saving extensions for Excel and other Excel spreadsheets, you're welcome to download the trial Version of our Ultimate Suite. If you like the tools and are able to buy a license, don't miss this special offer for our blog readers.

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

In some situations you might need to just calculate age in Excel and highlight cells that contain aged numbers that are lower or above a certain age.

In the event that your age calculation formula gives you the number of years that are complete that you have, you can design a standard conditional formatting rules that is based on a simple formula such as these:

  • 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 comprising the header column).

But what if your formula has age in months and years or in years days and months? In this instance you'll have to develop a rule that is based on a DATEDIF formula which calculates age from date of birth in years.

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

To create rules based on the formulas above, select the rows, or the cells you'd like to highlight. To do this, click the Home tab, then Styles and then click Conditional Formatting > New Rule... > Apply a formula to identify the cells that you want to format.

The full steps can be found in this article: how to create an automatic conditional formatting rule that is based on formula.

This is how you determine age with Excel. I hope the formulas were simple to master and that you'll give them some time in your worksheets. Thank you for reading and hope to see you here next week on our blog!

Comments

Post a Comment

Popular posts from this blog

what is cpu

  what is cpu Also known as the name for a central processor, or microprocessor, the CPU (pronounced"sea-pea-you") is the main processor (meaning that it is a cpu)of computers.   The CPU in computers handles all the commands it receives from programs in addition to operating the physical  runningm on the.   Computer.   This computer has processed, for instance instructions for the internet browser to open and show the web page on your computer.Tip The CPU is sometimes called"the"  the brain  which is also known as the computer.   However, it is more precise to refer to software as the brain. This is due to the CPU acting as an efficient calculator.   The CPU is exceptionally skilled in math, however, without software, it would not be able to do anything else.Note cpu full form  (  central processing unit  ) Many computer users who are new may wrongly refer to their computers as well as often, the screen being referred to as the...
Read more

Parts Per Million (ppm) Converter

Converter Parts Per Million (ppm) Parts Per Million (ppm) Converter for gases The converter calculates what volume of gas measured in ppm units, and converts it to quantities [mg/m 3] and as inverse. The unit ppm is employed in a variety of branches and ways. The use of ppm therefore should be mentioned in the fields below to input, in order to determine the way it will be converted to the correct unit. For more information on the use of ppm take a look at the instructions below.In this area, the entry field for is the Molecular Weight you could either choose from the drop-down menu or simply enter the molecular weight for the chemical. If you are unsure of the molecular value to you, then you should check out our Molecular Weight Calculator.When you enter your formula that you enter, either formula or the formula in units of [ppm] or in units of [mg/m(mg/m). 3. The value is converted immediately and is displayed at the lower right. This is how the importance o...
Read more

Calorie Calculator

Calorie Calculator The Calorie Calculator can be used to determine the number of calories an individual has to consume per day. The calculator can also provide the basic guidelines to gain or losing weight. Food Energy Converter The converter can be utilized to convert Calories and other common units for energy content in food. Related:BMI Calculator | Body Fat Calculator | Ideal Weight Calculator That Calorie Calculator was founded on several equations and its results are based upon an estimated average. It is believed that the Harris-Benedict Equation was one of the first equations to be used to calculate the Basal Metabolic Rates (BMR) which corresponds to how much energy is utilized daily at rest. The equation was revised in 1984 to make it more precise and was utilized through 1990, at which it was at that point that when the Mifflin-St Jeor Equation was introduced. In addition, the Mifflin Saint Jeor Equation will also calculate BMR and has been proved to be...
Read more