# Ageing Formula in Excel (All Things You Need to Know)

### Method 1 – Calculate Age in Years

We can use the combination of several functions to calculate the current 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)`

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

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)`

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

Since the YEAR function doesnâ€™t take day and month values into account, this formula is not accurate.

Read More:Â Applying Formula to Find Defect Aging

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

• In cell D5, add the following formula >> press the Enter key >> use the Fill Handle tool.
`=ROUNDDOWN(YEARFRAC(C5,TODAY()),0)`

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.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. Add the difference of only the month values between those two dates.

• Select the cell D5 >> enter the following formula >> press the Enter key >> drag down the Fill Handle
`=(YEAR(NOW())-YEAR(C5))*12+MONTH(NOW())-MONTH(C5)`

### Method 3 – Calculate Age in Days

• Insert the following formula in cell D5 >> press Enter >> use the Fill HandleÂ tool.
`=TODAY()-C5`

### Method 4 – Calculate Age in Years, Months, and Days in Excel

#### 4.1 If Birthdate Is in One Cell

• Enter the following formula in cell D5 >> press Enter >> drag down the Fill Handle
`=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:

• Enter the following formula in cell F6 >> press Enter >> drag down the Fill Handle
`=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?

### Method 1 – Calculate Age on a Certain Date in Excel

• Insert the following formula in cell E6 >> press Enter >> use the Fill HandleÂ tool.
`=DATEDIF(C6,D6,"Y")`

### Method 2 – Determine Date After N Years in Excel

• To determine the date when someone attains N years of age, enter the following formula in cell E5 >> press Enter >> use the Fill HandleÂ tool.
`=DATE(YEAR(C5)+D5,MONTH(C5),DAY(C5))`

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.

### Method 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 >> enter the following formula >> press Enter >> 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.

### Method 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 >> drag down the Fill HandleÂ icon.
`=DAYS360(C5,D5)`

### Method 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 >> enter the following formula >> press Enter >> use the Fill Handle tool to copy the formula in the remaining cells.
`=EDATE(C5,D5)`

## How to Create an Age Calculator in Excel?

• 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.

• 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.

• 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.

• Enter the following formula in cell C7.
`=IF(C10=1,TODAY(),"")`

• 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.

• Enter the formula in cell C13.
`=IFERROR(IF(C4<>"",IF(C10=1,DATEDIF(C4,C7,"M"),DATEDIF(C4,C8,"M")),""),"")`

• Insert the formula below in Cell C14.
`=IFERROR(IF(C4<>"",IF(C10=1,DATEDIF(C4,C7,"D"),DATEDIF(C4,C8,"D")),""),"")`

• If we select the first Option Button and enter a date of birth in cell C4, we will get the age in various units in the range C12:C14.

• If we select the second Option Button and enter any date in cell C8, weâ€™ll get the age.

Read More: Aging Analysis in Excel

## How to Categorize Aging Buckets in Excel?

Ageing buckets classify items based on their age. We will categorize various receivable amounts based on their overdue period. The sample dataset below will be used for illustration.

Click the image to see a detailed view

• Enter the following formula in cell D5 >> press Enter >> drag down the Fill HandleÂ icon.
`=B5+\$I\$4`

You can click the image to see a detailed view

• Enter the following formula in cell E5 >> press Enter >> use the Fill HandleÂ icon.
`=IF((TODAY()-D5)>0,TODAY()-D5,0)`

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.

Click the image to see a detailed view

• Enter the following formula in cell F5 >> press Enter >> use the Fill Handle tool to categorize the receivable amounts into various aging buckets.
`=VLOOKUP(E5,\$H\$7:\$I\$11,2,TRUE)`

Click the image to see a detailed view

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

• 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.

• The Format Cells dialog box will appear. Go to the Fill tab >> select any Background Color >> click OK.

• Click OK in the Edit Formatting Rule dialog box.

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

• 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.

You can click the image to see a detailed view

• 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`

You can click the image to see a detailed view

• The final output will look 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.

### 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. 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

Get FREE Advanced Excel Exercises with Solutions!
Seemanto Saha

Seemanto Saha graduated in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. He has been with ExcelDemy for a year, where he wrote 40+ articles and reviewed 50+ articles. He has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, he is working as a team leader for ExcelDemy. His role is to guide his team to write reader-friendly content. His interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF