Excel Formulas for Converting Days to Years

Sometimes you need to go from days to years in Excel.

Let's say you need to go from days to years on your worksheet and have some data already in the form of calendar dates.

If you have a person's birth date and want to know how many days or years have passed since their birthday, you can use a simple calculation.  

For whatever reason, this Excel tutorial will show you how to convert days to years.

Using Decimal Years to Count Down from Present Day

A simple mathematical formula exists for converting any given number of days into a decimal representation of years.

There are 365 days in a year, and we'll be using that number in our primary calculation.

To convert the number of days to years, simply divide the number of days by 365.

The dataset we're working with is depicted in the image below:

Above, you see numbers in days; you must change them to a decimal representation of years.

Let's pretend we're in cell A2 and use this formula:

Now, if we plug those numbers into the formula up top The outcome is as follows:

The converted years do not divide evenly by 365, as evidenced by the decimal form used in the cells.

While this is not the preferred method of representing dates, it is used by some programs.

Following this, you'll see how to show the year followed by the actual number of days for a given date.

Calculating Years and Remaining Days from a Given Number of Days

We're going back to the same data source we used before.

Assuming you are working in cell A2, you can use the following formula to convert the number of days to years and days:

Int(A2/365)&" Year(s)",INT(A2/365)&" Year(s) & "&MOD(A2,365)&" Days") =IF(MOD(A2,365)=0,A2/365&" Year(s)"

The IF() function is used here because it is a straightforward division and concatenation operator.

Applying the preceding formula The outcome is as follows:

Definition of Formulas

The formula is as follows:

• The primary function of the formula is an IF statement. Here, MOD(A2,365) = 0
• If this evaluates to True, then the value A2/365&"Year(s)" is returned.
• If this is true, it will return INT(A2/365)&" Year(s) & "&MOD(A2,365)&" Days".

This is the deal:

• When the first limit is subtracted from the second, the remainder can be found using the MOD formula. The formula MOD(A2,365) can be used to determine if the first limit is a multiple of the second. Using the MOD function, we can check if the value in A2 is evenly divisible by 365 in the given scenario.
• The result of dividing the number of days by A2/365 years will be an integer if and only if the value of A2 is divisible by 365.
• The result will be a decimal if the value of A2 is not a multiple of 365, and a whole number otherwise.
• The formula MOD(A2,365) is used to determine how many days are left. After dividing A2 by 365, we get the number of days left over.
• Our desired result, 2 years and 270 days, was obtained by concatenating the two string results ("Year(s)" and " Days"). The remainder after dividing 1002 by 365 is 272, so the answer is 2. To get the final result of 2 years and 272 days, we use this method.

Converting Years, Months, and Days from a Specific Day Number

Almost all of the same formula from before will be used, but we will change a few small details.

We will use the same data set as before, but this time we will organize the dates by year, month, and day.

The formula we'll be using is as follows (we'll assume we're in cell A2):

Year(s), Month(s), and Day(s) =INT(A2/365)&, "&INT(MOD(A2,365)/30)," "&INT(MOD(A2,365),30),"

Applying the preceding formula The end result is:

Explanation of a Formula

The formula we'll be using is very similar to one we used before.

The IF statement could be added at this point, but for the sake of brevity, we won't.

We are joining three concepts using the and operator:

• The initial term is "Year(s)" in INT(A2/365). The number of days is divided by 365 and the decimal is discarded, leaving only the integer value. The end result of the formula is a string with "Year(s)" appended to it. The formula is as follows:

=INT(2 745205479) Year(s)

• The second term is INT(MOD(A2,365)/30)&" Month(s)". For the remaining months, this is it. The MOD(A2,365) function checks the result of dividing the input number of days by 365 to find the remainder.  

The value is then divided by 30 (there are 30 days in a month) because it is likely to be greater than 12 months. Integers (whole numbers) are all that are retained after the INT function performs its magic. The formula result is extended by appending the string "Month(s)" The formula is as follows:

Integer Modulo 365/30 Months =INT(9 Day(s) Year(s)

• The third term is "Day(s)" MOD(MOD(A2,365),30). The number of days left is displayed here. After dividing the number of days by 365, the MOD(A2,365) function checks for the remainder. The resulting number of months is what we have left to work with. When the number of months is divided by 30, the MOD function looks for the remainder. Our remaining time is represented by this buffer. Concatenating the string "Day(s)" to the end of the formula result The formula is as follows:

SUBTRACT(SUBTRACT(1002,365),30) Day(s)

We get a duration of 2 years, 9 months, and 2 days from a total of 1002 days.

Excel's Year-to-Day Converter

Now let's do the opposite, going backwards in time and converting days into years.

In this case, too, we're assuming that 1 year has 365 days.

Our formula is as follows:

Users may have needed to calculate the number of years separating two dates in a number of contexts. This is one of the main justifications for the yearly calendar.

The number of days separating two dates can be calculated by subtracting them.

In this section of the guide, we'll go over the steps required to go from counting days to years. Learn the quick and simple methods of conversion presented here.

If you want to know how many years, months, or days separate two given dates, you can use Excel's DATED function for that purpose.

The formula we're using is as follows:

If (beginning_date, ending_date, unit), then

Whereas

• For this calculation, we will begin on start_date. That's a DATEtime value
• The last day of our calculation is indicated by the variable end_date. This is a DATE time stamp.
• We are using y to designate a unit for strings. This is a measure of time that can be subtracted from both the start_date and the end_date to determine the elapsed time.

Please take note that this is a parameter unit:

• "Y" - to indicate a year's worth of data
•   "M" - to indicate the month number
• D - to indicate the day of the week
• "YM" - used to reveal the current month's total after years have been subtracted
• "MD" - to indicate the number of days remaining after a month has been subtracted
•   "YD" - to express the number of days remaining after a year has been subtracted

The following data set serves as an example of the DATEDIF function in action:

Assume the information is located in cells A2 and B2. To calculate the number of days that have passed since two specific dates, use the DATEDIF formula:

To calculate the elapsed time between the two dates, use this formula.

To calculate the elapsed time in years and days, just use this formula:

A date is calculated as follows: = DATEDIF(A2,B2),"Year(s) "&DATEDIF(A2,B2),"YD")&" Day(s"

You can calculate the number of years and months that separate two dates using this formula:

Year(s) = DATEDIF(A2,B2),"Y"&" Month(s) "&DATEDIF(A2,B2),"YM"

How many years, months, and days have passed since those two dates?

= DATEDIF(A2,B2), "Year(s) "&DATEDIF(A2,B2), "YM")&" Month(s) "&DATEDIF(A2,B2), "MD")&" Day(s"

Note:

The DATEIF method yields more precise results than the MOD and INT methods.

This is why the DATEDIF function takes into account the fact that some months have fewer than 31 days (when compared to the previous method).

In this guide, you learned a few simple techniques for converting dates.

The DATEDIF function is the most efficient and reliable way to find the number of days between two dates.

Here's hoping this is a huge help for your work.