age calculator

How to 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 you can enter your birthdate within the appropriate cell, and then you'll get your age in a flash.

The calculator employs the following formulas to compute age based on the 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've got some experience using Excel Form controls, you could add an option to calculate age at a particular time such as in the image below:

For this, add the option buttons ( Developer tab > Insert > Form controls > Option Button) as well as link them to a cell. Also, create an IF/DATEDIF equation to calculate age that is at present or at the time specified 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", ""))

Then, you can nest the above functions in a way, and you'll get 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 that are in B10 and B11 work with the same logic. Of course, they're much simpler because they include only one DATEDIF function that returns age as the total of the months or days, respectively.

To get the full details for the details, I recommend you Download this Excel Age Calculator and investigate the formulas used in cells B9 and B11.

Download Age Calcqulator for Excel

Easy-to-use age calculator for Excel

Our users of the Ultimate Suite don't have to bother about making the age calculator in Excel - it's only a couple of clicks away:

  1. Select a cell that you'd like to include an age formula. Then, click 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 you will be able to go immediately to the tab for Age. tab.
  3. On the Age On the tab, you will find 3 things for you to specify:
    • Data of birth as a cell reference or a date in the format of mm/dd/yyyyyy.
    • Age at the current day or specific date.
    • Choose whether you want to determine age in days, months and years or in absolute age.
  4. Click the Formula to insert button.

Done!

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

As you might have observed, the formula created by the Excel age calculator has a formula that is more complicated than the formulas we've discussed so far but it also accommodates plural and singular units such as "day" and "days".

If you'd like to rid yourself of zero units such as "0 days", select the Do not show zero units checkbox:
Calculate age ignoring zero units. src="https://cdn.ablebits.com/_img-blog/age-excel/age-without-zero-units.png"/>

If you are curious to try the age calculator as well as to learn about 60 additional time-saving tools that can be added to Excel and Excel, we invite you to download a trial edition of the Ultimate Suite. If you're impressed with the tools and want to purchase a license, make sure you don't miss this special offer for our blog readers.

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

In some situations you might not need to only calculate age in Excel however, you may also want to highlight cells which contain aged numbers that are lower or over a particular age.

If the age calculation formula gives you the total number of years, then you can create a standard conditional formatting rules using a formula, like the following:

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

C2 is the highest cell in the column titled Age (not comprising the header column).

But what happens if the formula is displaying age in years and months, or in years, days and months? In this situation, you will have create a rule built 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:

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

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

The specific steps are available in this article: how to make the conditional formatting rule, that is based on formula.

This is the method you use to calculate age within Excel. I hope the formulas are simple to master and that you'll give them tests on your worksheets. Thank you for your time and hope to see you on our blog next week!

Comments

Post a Comment

Popular posts from this blog

What is the cpu

  What is the cpu Also known as for a central processor or microprocessor. The CPU (pronounced"seapeay-you")) is the unit that handles central computing (meaning for cpu)of computers.   The CPU in a computer handles every instruction it receives from both hardware and software  that is running on the computer.   For example, it processes instructions to launch a Web browser, which then opens and displays the website on your computer.Tip The CPU is usually described as"the"  brain  for computers.   It's more accurate to define software as the brain, or the CPU, as an extremely efficient calculator.   The CPU is extremely adept with numbers, but without the software , it won't be able to accomplish anything else.Note cpu full form  (  central processing unit  ) Many new computer users are prone to mistakingly calling their monitor or their computer the CPU.   If you refer to"the"  monitor or computer  (or the monitor) i...
Read more

Random Number Generator

Image
How to Generate Random Numbers on the TI-84 Plus In This Article By Jeff McCalla, C. C. Edwards It is likely that your TI-84 Plus calculator has a enormous number of digits in a list, called a random table which it utilizes in order to generate random numbers. Generating random integers In order to generate random integer ,follow these steps: To enable the randInt Wizard within the Math PROB menu, click If you're using a TI-84 Plus, there isn't a wizard for the randint command. In order to use the command you need to know the format: randint(lower, upper, [number of elements]).A wizard makes entering data easy. A wizard arranges the information you enter so that it fits the syntax of the command. Check out the wizard on one of the screens. Select the lower limit, and the upper limit that you want the random number you choose to be.You would like a random number that is between 1 and 100. Enter [ENTER] or press the down-arrow to move to the ne...
Read more