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

Download Practice Workbook

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.

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.

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.

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.

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

Method 2 – Calculate Age in Months

2.1  Combining DATEDIF and TODAY Functions

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

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

Method 3 – Calculate Age in Days

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

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

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

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.

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.

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.

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.

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.

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.

• Insert the following formula in cell C12.

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

• Enter the formula in cell C13.

• Insert the formula below in Cell C14.

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

You can click the image to see a detailed view

• Enter the following formula in cell E5 >> press Enter >> use the Fill Handle icon.

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.

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.

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:

For ages over 40, the formula is:

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.

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