Converting Days to Years in Excel Using Simple FormulasStep by Step Instructions with Screenshots

Written By:
Adiste Mae

Reviewed By:
FundsNet Staff

In Excel, there are times when you want to convert days to years.

As an example, suppose you have logged days in your worksheet which you need to convert into years.

Say you know the date of birth of a person and you wish to find out the number of days passed from that date to the current date, or maybe you wish to know the number of years. 

Whatever the reason is, we are going to show you how to convert days to years in Excel in this tutorial.

How to Convert Days to Years (in Decimal)

If you are given a number of days and you need to convert it to a number of years in decimal numbers, you can simply use a mathematical formula in this situation.

In a year there are 365 days and we are going to use this in our main formula.

You simply need to divide the number of days by 365 if you want to convert the days to years.

You can see in the image below the data set that we are using:

The numbers shown in the image above are in days, and you are needed to convert them to the number of years, in decimal places.

You can use this formula (say that we are working in cell A2):

Now, if we apply the formula given above. This is the result:

You can see in the cells that the converted number of years is not a multiple of 365 but is in a decimal format.

This format is not ideal for counting years but some applications use this format.

In the next section, you will see how to display the date in year format while the remainder is in a number of days format.

How to Convert Number of Days to Completed Years and Remaining Days

Again, we are using the same dataset from earlier:

You can use this formula in converting the number of days into years and days (let’s assume that we are working in cell A2):

Here we are using the IF() function which is a simple division and concatenates operator.

Now, if we apply the formula given above. This is the result:

Formula Explanation

Here’s how the formula works.

• The formula includes an IF condition in the main function. Here: MOD(A2,365)=0.
• This returns the result A2/365&”Year(s) if it is True.
• This returns the result INT(A2/365)&” Year(s) & “&MOD(A2,365)&” Days” if it is True.

Here are the terms:

• The MOD formula will find the remainder once its first limit is divided by its second. If the first limit is a multiple of the second, the formula MOD(A2,365) helps find it. In our given example, the MOD function helps us look if the A2’s value is divisible by 365.
• If A2’s value is divisible by 365, this means that we can divide the number of days by A2/365 years, and this will give us a whole number (Integer) as our result.
• If A2’s value is not divisible by 365, this means that we will not get a whole number as our result, and will give us a decimal.
• In finding the remaining number of days, the formula MOD(A2,365) is used. And this will give us the remaining days after A2’s value is divided by 365.
• To get our result, we combined the two results with strings (“Year(s)” and “ Days”) to get the following desired result: 2 years & 270 days. If we divide 1002 by 365, the result is 2, with a remainder of 272. This is how we get the result 2 years and 272 days.

How to Convert Number of Days to Years, Months, and Days

The formula that we are using is almost the same as the formula that we used earlier, but we are going to tweak some parts of the formula.

We are also going to use the same data set as earlier, but we will be using the years, months, and days format.

 Here is the formula that we are going to use (let’s assume that we are working in cell A2):

Now, if we apply the formula given above. This is the result:

Formula Explanation

The formula that we are going to use is almost similar to the formula that we used earlier.

Here we can add the IF statement, but in this case, we are not adding the statement to keep the formula simple.

Using the concatenate operator (&), we are combining 3 terms:

• INT(A2/365)&” Year(s)” is the first term. This function divides the number of days by 365, cuts the decimal, and only keeps the integer (whole number) part. The string “Year(s)” is concatenated to the end of the result of the formula. Here’s how the formula works:

• INT(MOD(A2,365)/30)&” Month(s)” is the second term. This is for the remaining number of months. The MOD(A2,365) function looks for the remainder attained once the number of days is divided by 365. 

The result is divided by 30 (30 days in one month), this is because the value is more likely be more than 12 months. The INT function cuts the decimal and only keeps the integer (whole number) part. The string “Month(s)” is concatenated to the end of the result of the formula. Here’s how the formula works:

• MOD(MOD(A2,365),30)&” Day(s)” is the third term. This is for the remaining number of days. The MOD(A2,365) function looks for the remainder attained once the number of days is divided by 365. This remainder will give us the remaining months. The MOD function looks for the remainder attained once the number of months is divided by 30. This remainder will give us the remaining days. The string “Day(s)” is concatenated to the end of the result of the formula. Here’s how the formula works:

As our result, 1002 days means 2 years, 9 months, and 2 days.

How to Convert Years to Days in Excel

Now, let’s reverse the order of the conversion, we are now converting a number of years back to a number of days.

Here we are also using the idea of 1 year = 365 days.

Here’s our formula:

How to Convert the Difference between Two Dates to Years

In some scenarios, users needed to find the difference between two dates in terms of years. This is one of the reasons they convert days to years.

By using simple subtraction, we can use the difference between the dates in counting the days between two dates.

In this section of the tutorial, we are discussing how to convert the number of days to the number of years. Here you will see how to convert fast and easily.

Excel has a function called the DATED function, it is dedicated to finding the number of years, months, or days between two different dates.

Here is the formula that we are using:

Whereas

• start_date is the initial date that we are using for our calculation. This is a DATE value.
• end_date is the ending date that we are using for our computation. This is a DATE value.
• unit is the string unit that we are specifying as “y”. This represents a unit of time, and this can be used to find the difference between the start_date and end_date.

Note: this is a parameter unit that we are using:

• “Y” – to tell the number of years
•  “M” – to tell the number of months
• “D” – to tell the number of days
• “YM” – to tell the number of months after subtracting years
• “MD” – to tell the number of days after subtracting whole months
•  “YD” – to tell the number of days after subtracting whole years

Here is our sample dataset on how we can use the DATEDIF function:

Let’s assume that the data are in cells A2 and B2. You can use the formula of DATEDIF to find the number of days between the two dates:

Also, use this formula to find the number of years between the two dates:

Use this formula to find the number of years and days between the two dates:

Use this formula to find the number of years and months between the two dates:

 Use the formula to find the number of years, months, and days between the two dates:

Note:

The DATEIF method has a more accurate result than the other method mentioned above (MOD and INT)

This is the reason that the DATEDIF function automatically considers the months that have 30 and 31 days automatically taken (which the other method used earlier did not consider).

This tutorial showed you some quick and easy methods on how to convert dates.

If you are looking for a way to look for the number between two dates, use the DATEDIF function, this method is the most recommended way of doing this.

Hope this helps you a lot with your project.