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

The image above , is an embedded Excel Online sheet, so don't hesitate to enter your birth date in the appropriate cell, and you will find your age in just a few seconds.

The calculator uses these formulas to calculate age in relation to the web page's date of birth in cell A3 and 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're familiar using Excel Form controls, you could add an option to calculate age at a specific date as illustrated in the following picture:

For this, add the 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 as of today's date or the date set by the user.

The formula works with 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 in a way, and you'll have the entire age calculation formula (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 of B10 and B11 follow the same logic. Of course, they are more straightforward because they contain just one DATEDIF function to calculate age as the total number of days or days, or both.

To get the full details for the details, I recommend you install the Excel Age Calculator and investigate the formulas within 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 an own age calculator in Excel - it is only few clicks away:

  1. Select a cell into which you would like to add an age formula. Click on the Ablebits Tools tab and then click the Date & Time group, then click the Date & Time Wizard button.
  2. The Date & Time Wizard will begin, and you will be able to go directly to the Age tab.
  3. On the Age On the tab, you will find 3 elements to indicate:
    • Data of birth as an individual cell reference or date in the format mm/dd/yyyyyy.
    • Age at the present time or particular date.
    • Decide whether to determine age in months, days year, or even exactly age.
  4. Click the Add formula button.

Done!

The formula is placed in the cell that you are currently in then you can double-click the fill handle to copy it to the column.

As you might have observed, the formula developed by the Excel age calculator has a formula that is more complicated than the formulas we've talked about so far however, it can be used for plural and singular units such as "day" and "days".

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

If you're eager to try this age calculator as well as to discover more time-saving tools for Excel and other Excel spreadsheets, you're welcome to download the trial edition of the Ultimate Suite. If you are impressed by the software and choose to purchase the license, don't overlook this exclusive offer only for blog readers.

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

In certain circumstances it is possible to not simply calculate age in Excel and highlight cells which contain ages that are under or over a specific age.

If the age calculation formula gives you the number of total years the formula can be used to design a standard conditional formatting rules with a formula such as these:

  • To draw attention to ages that are equal to or greater than 18: =$C2>=18
  • To highlight ages under 18: =$C2<18

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

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

If the birthdates are located in column B starting 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 show Ages over 65 (blue): =DATEDIF($B2, TODAY (),"Y")>65

To create rules based on the above formulas, select those cells or entire rows you'd like to highlight. To do this, click the Home tab, then Styles group, then select to create a new rule using Conditional Formatting... > Use a formula to determine which cells you should format.

The detailed steps can be found on this page: How to create the conditional formatting rule, basing on a formula.

This is the method you use to determine age within Excel. I hope that the formulas were simple to master and that you give them a some time in your worksheets. Thank you for reading and look forward to seeing you here next week on our blog!

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 the estimated number (in increments of [ppm[ppm]) and converts it into any of these units: (mg/m 3. [mg/m 3] ] and vice reverse. The unit ppm may be used in a variety of branches in a variety of ways. The use of ppm must be specified in the fields that follow in accordance with how it is converted into the appropriate unit. For more details on the meaning of ppm study the definition below.In this area, you can input of Molecular Weight . You can choose the drop-down menu, or you could input your own values to calculate the mass molecular of gas. If you aren't sure about the molecular weight to you, you can try our Molecular weight Calculator.When you enter either either or the calculation in increments [ppm] that are (mg/m 3 or [mg/m3The amount is converted instantly and displayed in the lower part of the screen. The significance of the calculation is de...
Read more

random number generator

Random Number Generator Make use of the generator to get an absolutely random digitally secure number. It creates random numbers that can be employed when precision of the result is important in shuffles of deck of cards in a game of Poker or drawing numbers to win giveaways, lottery or sweepstakes. What is the best way to select an random number between two numbers? You can make use of this random number generator for you to generate a reliable random number from any two numbers. For example, to generate an random number from one to 10 including 10, you need to enter 1 first in the input , and 10 in the second field then press "Get Random Number". Our randomizer will choose one of the numbers 1 to 10 randomly. To generate an random number between 1 and 100, utilize similar methods with 100, but it's in the 2nd field on the randomizer. To simulate a roll of dice the number range should be between 1-6 to simulate a typical six-sided dice. To generat...
Read more

pkdeveloper.in/ms-excel-formulas-list-pdf

MS Excel Formulas List PDF | MS Excel Formula & Functions Basics MS Excel Formulas List MS Excel Formula means is calculated by adding up numbers, then subtracting the total by the sum of the numbers. The following functions can be utilized to calculate the mean using Microsoft Excel: AVERAGE- Returns the average of numbers. Formulas are expressions that calculates the value of the cell. Excel already includes functions that can be utilized to build formulas that are pre-defined. MS Excel Formula & Functions Basics Formula construction: Formulae and functions begin with the letter =. Choose a range of cells or cell group that will be utilized in the formula by using your mouse Plus Add *Multiply, Subtract/Divide are the formulae's operators. BODMAS rules apply to the arithmetic (Brackets Over Division, Multiplication, Addition and Subtraction). Don't type variables into formulas (such for tax rate). Instead, place y...
Read more