This days to years calculator might be useful when you see or hear questions like this: Show
Naturally, you want to get a general sense of time, length of a period in general, which is years, months, and days, and you don't care if it is plus one day or minus one day because of a leap year or starting month. In this case, you can use the days to years calculator below. It just adds the specified number of days to the 1st of January 2017 (the first non-leap year after 2016) and then grabs a number of years, months, and days in the month passed between the start date and the end date. This allows you to make sense out of the number of days and understand how many years, months, and days it takes. Convert days to years, months and daysOf course, when you know the start and end dates for an event, you can use a precise version How many days are there between two dates? or Days between dates. The historical version.. There might be a number of reasons for wanting to convert days to years in Excel. 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. Converting Days to Years in Decimal in ExcelIf 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: If you want to convert each of the above numbers of days to the corresponding number of years, in decimals, you can use the following formula (assuming that you’re working on the input which is in cell A2): = A2/365 Let us apply this formula to the given data and see what results we get: As you can see, in the cells where the number of days is not a multiple of 365, the number of years is shown in decimals. This does not look realistic, but there are applications, where a notation like this for dates might be useful. If you want to display the date in the form of years and present the remainder of the days as simply the number of days, you can use the formula shown in the next section. Converting Number of Days to Completed Years and Remaining DaysIn 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: To convert days to years and days, you can use the following formula (assuming that you’re working on the input which is in cell A2): =IF(MOD(A2,365)=0,A2/365&" Year(s)",INT(A2/365)&" Year(s) & "&MOD(A2,365)&" Days") The above formula might look complex, but it uses only an IF statement, simple division, and a set of concatenation operators to get the job done. Here’s the result we get when the formula is applied to all the cells in the column: Explanation of the FormulaLet us take a moment to understand the above formula and why it worked.
Let us understand each of these terms:
Converting Number of Days to Years, Months, and DaysNow, what if you want to convert the number of days to years, months and days, instead of just years and days? Well, you can use the same formula pattern, with a few tweaks. Let’s see how. Let us use the same dataset to demonstrate how to convert days to the number of years, months, and days: To convert days to years, months and days, you can use the following formula (assuming that you’re working on the input which is in cell A2): =INT(A2/365)&" Year(s), "&INT(MOD(A2,365)/30)&" Month(s) and "&MOD(MOD(A2,365),30)&" Day(s)" Here’s the result we get when the formula is applied to all the cells in the column: Explanation of the FormulaThis formula is almost similar to the formula that we used in the previous section. You can choose to add the IF statement, but we chose not to add it so as to keep things simple. The formula basically combines 3 terms together using concatenation operators (&):
INT(1002/365) Year(s) =INT(2.745205479) Year(s) =2 Year(s)
INT(MOD(1002,365)/30) Month(s) =INT(272/30) Month(s) =INT(9.066666667) Month(s) =9 Month(s)
MOD(MOD(1002,365),30) Day(s) =MOD(272,30) Day(s) =2 Day(s) To sum it up, 1002 days also means 2 years, 9 months, and 2 days. Converting Years to Days in ExcelNow 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: =A2*365 Converting the Difference Between Two Dates into YearsMost 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: DATEDIF(start_date, end_date, unit) Here,
Note: The unit parameter can be any one of the following:
Let us see a few examples to understand how you can use the DATEDIF function. Following is a set of starting and ending dates: To find the number of days between the two dates, you can use the DATEDIF formula as shown below (assuming that you’re working on the inputs in cells A2 and B2): =DATEDIF(A2,B2,”D”) Similarly, to find the number of years between the two dates, use: =DATEDIF(A2,B2,”Y”) To find the number of years and days between the two dates, use: = DATEDIF(A2,B2,”Y”)&” Year(s) ”&DATEDIF(A2,B2,”YD”)&” Day(s)” To find the number of years and months between the two dates, use: = DATEDIF(A2,B2,”Y”)&” Year(s) ”&DATEDIF(A2,B2,”YM”)&” Month(s)” To find the number of years, months, and days between the two dates: = DATEDIF(A2,B2,”Y”)&” Year(s) ”&DATEDIF(A2,B2,”YM”)&” Month(s) ”&DATEDIF(A2,B2,”MD”)&” Day(s)” Note 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. Other Excel tutorials you may also find useful:
How do you convert days into years in simple interest?In simple interest when the time is given in months and days we always need to convert into years. Notes: (i) When “T' i.e., the time is given in months then it should be divided by 12 to convert into years. (ii) When “T' i.e., the time is given in days then it should be divided by 365 to convert into years.
|