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

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

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.

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.

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.

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.

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

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.

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.

3. Calculate Age in Days

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

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.

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.

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.

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.

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.

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.

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.

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.

• Afterward, insert the following formula in cell C12.

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

• Similarly, enter the formula in cell C13.

• Finally, insert the formula below in Cell C14.

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

Click the image to see a detailed view

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

You can click the image to see a detailed view

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

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.

Click the image to see a detailed view

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

Click the image to see a detailed view

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.

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.

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