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 here is an embedded Excel Online sheet, so take the time to enter your birthdate in the corresponding cell and you'll know your age in just a second.

The calculator utilizes the formulas listed below to calculate age based on the "" date of birth in cell A3 and 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 got some experience with Excel Form controls, you may add an option that allows you to compute age at a certain date such as in the following picture:

To accomplish this, add two option buttons ( Developer tab > Insert > Form controls > Option Button) and connect them to a cell. Write an IF/DATEDIF formula to get age either at today's date 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 will get 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 that are in B10 and B11 are based on an identical logic. Of course, they're more straightforward because they contain only one DATEDIF function that returns age as the total of the months or days, respectively.

To find out more I encourage you to download the Excel Age Calculator and investigate the formulas found in cells B9: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 create an own age calculator in Excel - it is only one click away:

  1. Select a cell where you would like to add an age formula. Go to the Ablebits Tools tab, then the Date and Time group, and click the Date & Time Wizard button.
  2. When you click on the Date & Time Wizard will begin, and you will be able to go right to the aged tab.
  3. On the Age Tab, there are 3 elements to indicate:
    • Birth date data as cell reference or date in the format of mm/dd/yyyyyy.
    • Age at the current time or particular date.
    • Choose whether you want to determine age in terms of days, months years, years, or the exact age.
  4. Click the Add formula button.

Done!

The formula is added to the selected cell momentarily after which you double-click it to save it down the column.

As you've probably noticed, the formula created through Excel's Excel age calculator Excel age calculator is more complex than the ones we've discussed so far but it also accommodates singular and plural of time units, such as "day" and "days".

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

If you're eager to try this age calculator as well as to learn about 60 additional time-saving extensions for Excel You're invited to download a free trial edition of the Ultimate Suite. If you're satisfied with the tool and are able to buy an upgrade, don't be averse to this deal for our blog readers.

How do I highlight certain age groups (under or over a particular age)

In some situations you might need to only calculate age in Excel however, you may also want to highlight cells with the ages that are less or over a particular age.

When your age calculation formula results in the number of years that are complete the formula can be used to design a standard conditional formatting rules based on a simple formula, such as:

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

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

What happens if your formula will display age in months and years, or in years, days and months? In this case, you will have create a rule basing it on a DATEDIF formula that calculates age from date of birth in years.

If the birthdates occur in column B that 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 age groups above 65 (blue): =DATEDIF($B2, TODAY (),"Y")>65

To make rules based on the formulas above, select those cells or entire rows that you wish to highlight, click the Home tab, then Styles group, and select the New Rule button... and then use a formula to determine which cells you should format.

The complete steps are listed Here: The steps to create the conditional formatting rules built on formula.

This is how you determine age using Excel. I hope the formulas are simple to master and that you give them a the chance to test them on 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

LENGTH CONVERTER

Image
  LENGTH CONVERTER Select the length you want to convert and then write the amount you wish to convert into another unit of length into the "From" field, and click on the unit to which you wish to convert into the "To" field.   The results will be shown in the "To" field right after you click on any unit. Length , as you may know, refers to an accurate  measurement of anything from end to end, and it isn't easy to be exact when the conversion of miles into kilometers.   Sometimes, we require an estimation of others arrival or to make it to our destination this is the reason length converter can be really useful for calculations.   Measurement converter is utilized depending on the circumstances and, since you can convert any type of length, it will assist you with your mathematical difficulties as well.   To determine how far an individual is, you can convert their distance in  kilometers or miles and calculate the exact time of advent. The world is bu...
Read more

random number generator

TEMPLATE DESCRIPTION It's enjoyable to find out how long you have spent on this planet. Are you aware of how many hours or days have passed since the day you were married? Check out your Family Age Calculator in Excel to get the answer! This Excel template is a straightforward calculator for family members. It is comprised of two parts: Dashboard Age Counter Dashboard It has a beautiful design and a simple dashboard. You'll be able to enter the names of family members, their birthday and it will also display how many days have passed since your parents' marriage. Age Counter: Here the template will calculate different things in relation to the age you input: How many Months, Days, Minutes, Hours did you have to live! Calculations will be done according to the current date on your system , using the Today() function of Microsoft Excel. You can print the Excel spreadsheet template, and export it as PDF as you prefer. Are you a m...
Read more