For example, you might have a count of days that have passed and you might want to calculate how many years it makes.
You might need to find the age of a person given the number of days from their birth to the current date, or you might need to calculate the number of years to a certain date.
Whatever the reason, in this tutorial we will show you some quick tricks to convert days to years in Excel.
Table of Contents
Converting Days to Years in Decimal in Excel
If you have the number of days and want to get the number of years in decimal numbers, then there’s a very simple mathematical formula that you can use.
This formula simply makes use of the fact that 1 year contains 365 days.
So if you want to convert the number of days to years, all you need to do is divide the number of days by 365.
Let us say you have the following list of the number of days:
How to Remove Year from Date in Excel?
Converting Number of Days to Completed Years and Remaining Days
In this section, we will show you a formula that you can use if you want to convert the number of days to the number of years and days.
Let us use the same list of days to demonstrate this formula:
How to Remove Year from Date in Excel?0
- The third term is MOD(MOD(A2,365),30)&” Day(s)”. This gives us the remaining number of days. The MOD(A2,365) function finds the remainder obtained when the number of days is divided by 365. This remainder gives the number of remaining months. The outer MOD function then finds the remainder obtained when this number of months is divided by 30. The remainder obtained is the number of remaining days. Finally the formula concatenates the string “Day(s)” to the result. So if the number of days is 1002, this formula returns:
To sum it up, 1002 days also means 2 years, 9 months, and 2 days.
Also read: How to Remove Year from Date in Excel?4Converting Years to Days in Excel
Now let’s take a look at how to do the reverse – how to convert the number of days back to years.
This is really very simple since we again need to use the same concept of 1 year = 365 days.
So the formula will be:
Converting the Difference Between Two Dates into Years
Most Excel users commonly need to find the number of years between two dates. This is usually the main reason they might want to convert days to years.
They might know that the difference between two dates can be found by simple subtraction, so they might have done that and got the number of days between the two dates.
If you came to this page looking for a way to convert the number of days obtained (after subtraction) to years, we have got an easier and quicker solution for you.
Excel has a special function specifically dedicated to finding out the number of days, months, or years between two specified dates. The function is the DATEDIF function.
The syntax for the DATEDIF function is:
Also read: How to Remove Year from Date in Excel?6Here,
- start_date is the starting date to consider in the calculation. This could be a DATE value or a reference to a date value.
- end_date is the ending date to consider in the calculation. This could also be a DATE value or a reference to a date value.
- unit is a string that represents a unit of time. So if you want to find the number of years between the start_date and end_date, the unit string can be specified as “y”.
Note: The unit parameter can be any one of the following:
- “Y” – to show number of years
- “M” – to show number of months
- “D” – to show number of days
- “MD” – to show number of days after subtracting whole months
- “YM” – to show number of months after subtracting whole years
- “YD” – to show number of days after subtracting whole years
Let us see a few examples to understand how you can use the DATEDIF function. Following is a set of starting and ending dates:
How to Remove Year from Date in Excel?7
How to Remove Year from Date in Excel?8
How to Remove Year from Date in Excel?9
To find the number of years and months between the two dates, use:
=IF(MOD(A2,365)=0,A2/365&" Year(s)",INT(A2/365)&" Year(s) & "&MOD(A2,365)&" Days")0To find the number of years, months, and days between the two dates:
=IF(MOD(A2,365)=0,A2/365&" Year(s)",INT(A2/365)&" Year(s) & "&MOD(A2,365)&" Days")1Note that the DATEDIF method provides more accurate results than the methods using MOD and INT (that we showed at the start of this tutorial).
This is because the DATEDIF function automatically takes into consideration the months that have 30 and 31 days (which the first few methods did not take into account, to avoid making the formulae too complex).
In this tutorial, we showed you some quick tricks to convert days to years in Excel.
In case you were looking for a way to find the number of years between two dates, we have also shown how to use the DATEDIF function to get this done quickly. We hope this was helpful.