In this Excel tutorial, we will learn about the ageing formula in Excel. We will use these formulas to:

– Calculate age in years, months, and/or days format

– Calculate a date after N years

– Determine networking days, due dates, and project durations

– Create an ageing calculator and categorize aging buckets

– And highlight ages based on criteria

While preparing this article, we used Microsoft 365 for our calculations. However, you can find all the functions used in ageing formulas in Excel 2010 or newer versions.

Ageing formulas allow us to determine the duration between two dates, making it handy for tasks like tracking project progress, monitoring defects, or managing payment deadlines. They are also important for categorizing tasks into different aging buckets based on fixed time intervals.

In the following image, we show the most frequently used ageing formulas to calculate age in years, months, or days.

**Download Practice Workbook**

**Table of Contents**Expand

## Why Should We Learn Ageing Formula in Excel?

Age-based calculations are very common in our day-to-day life. Ageing formulas in Excel can help us to perform these calculations faster than the manual method. Some common instances of using ageing formulas to calculate age are:

– Arranging birthdays

– Determining employment period

– Finding the age of an insurance policy

– Calculating task completion time, etc.

## How to Apply Ageing Formula with Birthdate in Excel?

In this section, we will apply ageing formula with birthdate to calculate the current age in years, months, and/or days units.

### 1. Calculate Age in Years

We can use the combination of several functions to calculate the current age in years. Here are 4 ideal methods to calculate age in years.

#### 1.1 Use INT and TODAY Functions

- Insert the following formula in cell
**D5**>> press**Enter**>> use the**Fill Handle**tool.

`=INT((TODAY()-C5)/365)`

Here, **the TODAY function** returns the current date. And, **the INT function** rounds down the number to its nearest integer.

However, since we are using 365 (the number of days in a year) in the formula, this formula is inaccurate for intervals with **Leap years** in between. As Leap years occur once every four years, we can divide the number of days between the current date and birthdate by 365.25 instead. But this will also be incorrect for intervals that don’t include any Leap years.

#### 1.2 Apply YEAR and NOW Functions

- Insert the formula below in cell
**D5**>> press the**Enter**key >> drag down the**Fill Handle**icon.

`=YEAR(NOW())-YEAR(C5)`

Here, **the NOW function** returns the current time and date. **The YEAR function** returns the year value of the current date and birth date.

However, since the **YEAR** function doesn’t take day and month values into account, this formula is not very accurate.

**Read More: **Applying Formula to Find Defect Aging

#### 1.3 Use ROUNDDOWN, YEARFRAC, and TODAY Functions

- In cell
**D5**, type in the following formula >> press the**Enter**key >> use the**Fill Handle**tool.

`=ROUNDDOWN(YEARFRAC(C5,TODAY()),0)`

Here, the **TODAY** function returns the current date.

**The YEARFRAC function** returns the year fraction for the number of days passed between the date of birth and the current date. And, **the ROUNDDOWN function** rounds down the year fraction value.

#### 1.4 Combine DATEDIF and TODAY Functions

- Insert the following formula in cell
**D5**>> press the**Enter**key >> drag down the**Fill Handle**icon.

`=DATEDIF(C5,TODAY(),"Y")`

The **TODAY** function returns the current date. And, **the DATEDIF function** returns the difference between the date of birth and the current date.

**Note**: The

**DATEDIF**function doesn’t appear in the suggestions while typing the formula as it is not available in Excel IntelliSense.

### 2. Calculate Age in Months

We can apply any of the previously used formulas with some alterations to calculate the current age in months. Here, I’ll show two methods: combining the **DATEDIF **and **TODAY** functions and using the **YEAR**, **NOW**, and **MONTH** functions.

#### 2.1 Combining DATEDIF and TODAY Functions

- In cell
**D5**, insert the formula below >> press the**Enter**key >> drag down the**Fill Handle**tool.

`=DATEDIF(C5,TODAY(),"M")`

#### 2.2 Using YEAR, NOW, and MONTH Functions

We can calculate the number of years between two dates and multiply the result by 12 to get the number of months between those years. After that, we have to add the difference of only the month values between those two dates.

- Select the cell
**D5**>> type in the following formula >> press the**Enter**key >> drag down the**Fill Handle**icon.

`=(YEAR(NOW())-YEAR(C5))*12+MONTH(NOW())-MONTH(C5)`

### 3. Calculate Age in Days

- Insert the formula below in cell
**D5**>> press the**Enter**key >> use the**Fill Handle**tool.

`=TODAY()-C5`

### 4. Calculate Age in Years, Months, and Days in Excel

Sometimes we may require the exact value of the current age instead of the year value only. Here, are two simple cases of calculating age in Years, Months, and Days.

#### 4.1 If Birthdate Is in One Cell

- Type the following formula in cell
**D5**>> press the**Enter**key >> drag down the**Fill Handle**icon.

`=DATEDIF(C5,TODAY(),"Y") & " Years, " & DATEDIF(C5,TODAY(),"YM") & " Months and " & DATEDIF(C5,TODAY(),"MD") & " Days"`

__Formula Breakdown__

**TODAY()**

Returns the current date.

**DATEDIF(C5,TODAY(),”Y”)**

Calculates the difference of years between the date of birth and the current date.

**DATEDIF(C5,TODAY(),”YM”)**

Determines the difference of months between the date of birth and the current date while ignoring year values.

**DATEDIF(C5,TODAY(),”MD”)**

Calculates the difference of days between the date of birth and the current date while ignoring year and month values.

**DATEDIF(C5,TODAY(),”Y”) & ” Years, ” & DATEDIF(C5,TODAY(),”YM”) & ” Months and ” & DATEDIF(C5,TODAY(),”MD”) & ” Days”**

Concatenates the calculated years, months, and days values.

#### 4.2 If Birthdate Is Split into Three Different Cells

Sometimes, the birthdate is stored in three different cells containing the date, month, and year. In those cases:

- Apply the formula below in cell
**F6**>> press the**Enter**key >> drag down the**Fill Handle**icon.

`=DATEDIF(DATE(E6,MONTH(DATEVALUE(D6&"1")),C6),TODAY(),"Y")&" Years, "&DATEDIF(DATE(E6,MONTH(DATEVALUE(D6&"1")),C6),TODAY(),"YM")&" Months and "&DATEDIF(DATE(E6,MONTH(DATEVALUE(D6&"1")),C6),TODAY(),"MD")&" Days"`

__Formula Breakdown__

**DATEVALUE(D6&”1″)**

Converts the text formatted month names into numbers that represent month indices in MS Excel date-time code.

**MONTH(DATEVALUE(D6&”1″))**

Returns month indices.

**DATE(E6,MONTH(DATEVALUE(D6&”1″)),C6)**

Joins the birthday, month, and year information to return the birthdate.

**TODAY()**

Returns the current date.

**DATEDIF(DATE(E6,MONTH(DATEVALUE(D6&”1″)),C6),TODAY(),”Y”)**

Calculates the difference of years between the date of birth and the current date.

**DATEDIF(DATE(E6,MONTH(DATEVALUE(D6&”1″)),C6),TODAY(),”YM”)**

Determines the difference of months between the date of birth and the current date while ignoring year values.

**DATEDIF(DATE(E6,MONTH(DATEVALUE(D6&”1″)),C6),TODAY(),”MD”)**

Calculates the difference of days between the date of birth and the current date while ignoring year and month values.

**DATEDIF(DATE(E6,MONTH(DATEVALUE(D6&”1″)),C6),TODAY(),”Y”)&” Years, “&DATEDIF(DATE(E6,MONTH(DATEVALUE(D6&”1″)),C6),TODAY(),”YM”)&” Months and “&DATEDIF(DATE(E6,MONTH(DATEVALUE(D6&”1″)),C6),TODAY(),”MD”)&” Days”**

Joins the calculated years, months, and days values.

## Can You Give More Examples of Ageing Formula in Excel?

There are numerous examples available for ageing formulas in Excel. We can apply ageing formulas for calculating age on a certain date, determining dates after N years, calculating net working days, etc. Here are a few more examples of ageing formulas in Excel.

### 1. Calculate Age on a Certain Date in Excel

- Insert the following formula in cell
**E6**>> press the**Enter**key >> use the**Fill Handle**tool.

`=DATEDIF(C6,D6,"Y")`

### 2. Determine Date After N Years in Excel

- To determine the date when someone attains N years of age, type in the following formula in cell
**E5**>> press the**Enter**key >> use the**Fill Handle**tool.

`=DATE(YEAR(C5)+D5,MONTH(C5),DAY(C5))`

Here, the **YEAR**, **MONTH**, and **DAY** functions respectively return the year, month, and day value of the birthdate. And, **the DATE function** appends the day, month, and modified year values into a date.

### 3. Calculate the Number of Working Days Between Two Dates

We can use **the NETWORKDAYS function** to calculate the number of working days between a start and a due date. This function subtracts weekly, federal, and floating holidays from total days.

- Select the Cell
**E5**>> type in the following formula >> press the**Enter**key >> drag down the**Fill Handle**icon.

`=NETWORKDAYS(C5,D5,$G$5:$G$9)`

**Note**: The

**NETWORKDAYS**function considers Saturday and Sunday as weekly holidays. To customize the weekends, we can use

**the NETWORKDAYS.INTL function**instead.

### 4. Determine Project Duration Based on a 360-Day Year System

In several accounting calculations, we have to assume a 360-day year system (twelve 30-day months). In such cases, we can use the **DAYS360 **function to calculate the difference between a start date and a due date.

- In Cell
**E5**, insert the following formula >> press**Enter**key >> drag down the**Fill Handle**icon.

`=DAYS360(C5,D5)`

### 5. Calculate Due Date Based on Project Duration in Months

We can use **the EMONTH function** to calculate due dates by adding or subtracting months from the start date.

- Select cell
**E5**>> type in the following formula >> press**Enter**key >> use the**Fill Handle**tool to copy the formula in the remaining cells.

`=EDATE(C5,D5)`

## How to Create an Age Calculator in Excel?

We can create an age calculator by following a few simple steps in Excel. First, we require a dataset like the following in an Excel worksheet.

- Go to the
**Developer**tab >> click on the**Insert**dropdown menu >> select the**Option Button**Form Control.

The **Developer** tab is not available in Excel by default. You can add it from **File >> Options >> Customize Ribbon >> Main Tabs** directory.

- Then drag your mouse over cell
**B7**to insert the Option Button. Delete the caption of the Option Button and repeat these steps to add another Option Button over cell**B8**.

- Now, select any Option Button and right-click on your mouse >> select the
**Format Control**option from the pop-up context menu.

- A user form like the following will appear. Go to the
**Control**tab >> select**C10**as the**Cell link**>> click on the**OK**button.

- Now, insert the formula below in cell
**C7**.

`=IF(C10=1,TODAY(),"")`

- Afterward, insert the following formula in cell
**C12**.

`=IFERROR(IF(C4<>"",IF(C10=1,DATEDIF(C4,C7,"Y") & " Years, " & DATEDIF(C4,C7,"YM") & " Months and " & DATEDIF(C4,C7,"MD") & " Days",DATEDIF(C4,C8,"Y") & " Years, " & DATEDIF(C4,C8,"YM") & " Months and " & DATEDIF(C4,C8,"MD") & " Days"),""),"")`

Since we haven’t entered the date of birth yet, cell **C12** shows empty.

- Similarly, enter the formula in cell
**C13**.

`=IFERROR(IF(C4<>"",IF(C10=1,DATEDIF(C4,C7,"M"),DATEDIF(C4,C8,"M")),""),"")`

- Finally, insert the formula below in Cell
**C14**.

`=IFERROR(IF(C4<>"",IF(C10=1,DATEDIF(C4,C7,"D"),DATEDIF(C4,C8,"D")),""),"")`

- Now, if we select the first Option Button and enter someone’s date of birth in cell
**C4**, we will get his/her age in various units in the range**C12:C14**.

- However, if we select the second Option Button and enter any date in cell
**C8**, we’ll get his/her age on that specific age.

**Read More: **Aging Analysis in Excel

## How to Categorize Aging Buckets in Excel?

Ageing buckets classify items based on their age. Here, we’ll categorize various receivable amounts based on their overdue period. We will use the following dataset for this section.

- First, enter the formula below in cell
**D5**>> press the**Enter**key >> drag down the**Fill Handle**icon.

`=B5+$I$4`

- Afterward, in cell
**E5,**type in the following formula >> press the**Enter**key >> use the**Fill Handle**icon.

`=IF((TODAY()-D5)>0,TODAY()-D5,0)`

Here, the **TODAY** function returns the current date and **the IF function** checks whether the days are overdue. If the days are overdue, it returns the overdue days. Else, it returns 0.

- Finally, apply the following formula in cell
**F5**>> press the**Enter**key >> use the**Fill Handle**tool to categorize the receivable amounts into various aging buckets.

`=VLOOKUP(E5,$H$7:$I$11,2,TRUE)`

## How to Highlight Ages Under or Over a Specific Age in Excel?

Sometimes, we may need to highlight cells that contain ages under or over a specific age along with calculating age. Here, we will use **Conditional Formatting** to highlight cells that contain age values under 30, between 30 and 40, and over 40 in the following dataset.

- First, select the range
**B5:D10**>> click the**Conditional Formatting**drop-down from the**Home**tab >> select the**New Rule**option.

- In the
**Edit Formatting Rule**dialog box, select the**Use a formula to determine which cells to format**option >> set the formula to the following >> click the**Format**button.

`=$D5<30`

This formula is to highlight ages under 30.

- At this time, the
**Format Cells**dialog box will appear. Go to the**Fill**tab >> select any**Background Color**>> click the**OK**button.

- Then, click the
**OK**button in the**Edit Formatting Rule**dialog box.

- This will highlight the rows that contain ages under 30.

- Now, select the range
**B5:D10**again >> click the**Conditional Formatting**drop-down from the Home tab >> select the**Manage Rules**option.

- Click the
**New Rule**command in the**Conditional Formatting Rules Manager**window.

- Repeat the previous steps to add two new rules and click the
**OK**button.

For ages between 30 and 40, the formula is:

`=AND($D5>=30,$D5<=40)`

For ages over 40, the formula is:

`=$D5>40`

- The final output looks like the following.

## What Are the Things to Remember?

- If the calculated age value appears in
**Date**format, change the formatting to**General**. - Ageing formulas don’t apply for dates before 1st January 1900.
- The date format may change to
**DD/MM/YYYY**format based on your computer’s Time and Date settings.

## Conclusion

This ends our article to learn about ageing formula in Excel. We showed various formulas that calculate age in different units based on birthdates. We explained other formulas to calculate net working days, project due dates, and duration of a project. These formulas are very useful in project management and other financial analysis.

We also showed the steps to create a calculator that calculates age just from an input date. Aging buckets were categorized using ageing formulas as well, which is very valuable in financial and investment analysis. We hope these demonstrated formulas were helpful for your requirements. Feel free to share your thoughts in the comment section.

## Frequently Asked Questions

### 1. How do I handle Leap years when calculating age in Excel?

** Answer**: Instead of 365 days, you can use 365.25 days in your ageing formulas as Leap years come once every four years. However, this will return inaccurate results sometimes. Hence, using the

**DATEDIF**function is more convenient.

### 2. How do I ensure that the calculated age updates automatically in Excel as the current date changes?

** Answer**: Instead of using the actual current date value (directly or by cell reference), we can use the

**TODAY**or

**NOW**function to get the current date.

### 3. Is it possible to calculate age in Excel without using a formula?

** Answer**: Yes, we can calculate age in Excel without using a formula. This requires using the

**POWER Query**tool. It consists of various built-in features that let us calculate age in Excel.

## Ageing Formula in Excel: Knowledge Hub

**<< Go Back to Formula List | Learn Excel**