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 below is an embed Excel Online sheet, so you can enter your birthdate into the appropriate cell and you'll find your age in just a few seconds.

The calculator employs the formulas below to calculate age using the formulas below based on the date of birth in cell A3 as well as 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 had some experience working with Excel Form controls, you could add an option to calculate age on a particular date as shown in the screenshot below:

To do this, you need to add two buttons for options ( Developer tab > Insert > Form controls > Option Button) and connect them to a cell. Then, you can write an IF/DATEDIF formula that will give you age or at the time of today's date or the date set by the user.

The formula is based on 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", ""))

Then, you can nest the above functions together, and you'll get the entire age calculator (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 found in B10 and B11 are based on similar logic. Of course, they're more straightforward because they contain only one DATEDIF function that returns age as the total number of months or days, respectively.

For more information To find out more, Download the Excel Age Calculator and investigate the formulas found in cells B9:B11.

Download Age Calcqulator for Excel

Easy-to-use age calculator for Excel

Our users of the Ultimate Suite don't have to make the age calculator in Excel - it's just two clicks away:

1. Choose a cell in which you would like to add an age formula. Go to the Ablebits Tools tab, then the Date and Time group, then click the Date and 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 three items to be specified:
   • Birth date data as an individual cell reference or date formatted in the format mm/dd/yyyyy.
   • Age at the current day or an exact date.
   • Select whether to determine age in terms of days, months or years, or choose the precise age.
4. Click the Formula to insert button.

Done!

The formula is placed in the cell you have selected after which you double-click onto the handle for fill to paste it into the column.

You may have observed, the formula developed using the Excel age calculator can be more complicated than the formulas we've talked about so far however, it can be used for the singular and plural of time units like "day" and "days".

If you'd like to rid yourself of units that are zero like "0 days", select the Don't display zero units checkbox:
src="https://cdn.ablebits.com/_img-blog/age-excel/age-without-zero-units.png"/>

If you're interested to try the age calculator as well as to find 60 other time-saving tools for Excel and Excel, we invite you to download a free trial edition of the Ultimate Suite. If you are impressed by the software and choose to purchase an account, don't forget to take advantage of this offer exclusively for our blog readers.

How do you highlight specific age groups (under or over a certain age)

In certain situations it is possible to not just calculate age in Excel however, you may also want to highlight cells that contain age ranges that are below or above a certain age.

When your age calculation formula gives you the total number of years and you want to create an ordinary conditional formatting rule using a formula such as these:

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

C2 is the highest cell in the column titled Age (not not including column head).

What happens if your formula shows age in months and years or in years, days and months? In this scenario you'll need create 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 the ages above 65 (blue): =DATEDIF($B2 (TODAY (),"Y")>65

To make rules based on the formulas above, choose the rows or cells that you wish to highlight, click the Home tab, then Styles, then select the New Rule button... > Apply an equation to decide the cells that you want to format.

The complete steps are available in this article: how to create an underlying conditional formatting rule that is based on formula.

This is the method you use to determine age using Excel. I hope that the formulas were simple to master and that you give them a an attempt in your worksheets. Thank you for taking the time to read and we hope to see you again here next week on our blog!