There can be many reasons to convert days to years in Excel.
For example, you may have a number of days that have passed and you want to calculate how many years ago.
You may need to find someone's age by considering the number of days from birth to the current date, or you may need to calculate the number of years to a specific date.
Whatever the reason, in this tutorial we will show you some quick tricks to do itConvert days to years excel.
table of contents
Convert days to years to decimal in Excel
If you have the number of days and want to get the number of years in decimals, there is a very simple math formula you can use.
This formula simply takes advantage of the fact that 1 year has 365 days.
So if you want to convert the number of days to years, just divide the number of days by 365.
Suppose you have the following list with the number of days:
If you want to convert the number of days before to the corresponding number of years,in decimals, you can use the following formula (assuming you're working on the input in cell A2):
= A2/365
Let's apply this formula to the given data and see the results we get:
As you can see, in cells where the number of days is not a multiple of 365, the number of years is displayed in decimal places.
That doesn't sound realistic, but there are applications where such notation for dates can be useful.
If you want to display the date as years and the remaining days as evennumber of days, you can use the formula shown in the next section.
Also read:How to remove year from date in Excel?Conversion of the number of days into full years and remaining days
In this section we will show you a formula that you can use when you want to convert the number of days to the number of years.jdiam.
Let's use the same list of days to demonstrate this formula:
To convert days to years and days you can use the following formula (assuming you are working on the input in cell A2):
=SE(MOD(A2,365)=0,A2/365&" Ano(s)",INT(A2/365)&" Ano(s) & "&MOD(A2,365)&" Dias")
The formula above may seem complex, but it uses only oneYES statement, simple division and a bunch of concatenation operators to get the job done.
Here is the result we get when the formula is applied to all cells in the column:
Explanation of the formula
Let's take a moment to understand the formula above and why it worked.
- The main function of this formula consists of an IF condition. The formula first finds out if MOD(A2,365)=0.
- if soReal, then returns the result A2/365&”year(s)”.
- if soNOT CORRECT, then returns the result INT(A2/365)&" year(s) & "&MOD(A2,365)&" days".
Let's understand each of these terms:
- The MOD formula finds the remainder when its first parameter is divided by the second. In this way, the formula MOD(A2,365) =0 can help us find out if the first parameter is a multiple of the second. In our example, this MOD function helps us find out if the value in A2 is divisible by 365.
- If the value in A2 is divisible by 365, that means we can perfectly divide the number of days in A2 by 365 years and get an integer (or integer) back.
- If the value is in A2Notis divisible by 365, this means that dividing by 365 gives a decimal or remainder of days.
- To find this number of days left, we use the formula: MOD(A2,365). This tells us how many days are left after dividing the value in A2 by 365.
- We combine these results with the strings "Year(s)" and "Days" to get the following result:2 years and 270 days.If 1002 is divided by 365, we get a quotient of 2 and a remainder of 272. So1002 diaalso means2 years and 272 days!
Conversion of the number of days into years, months and days
Now what if you want to convert the number of days?years, months and days, instead of onlyyears and days?
Well, you can use the same standard formula with a few tweaks.
Let's see how.
Let's use the same dataset to demonstrate how to convertdiamAllNumber of years, months and days:
To convert days to years, months, and days, you can use the following formula (assuming you're working on the input in cell A2):
=INT(A2/365)&" year(s), "&INT(MOD(A2,365)/30)&" month(s) and "&MOD(MOD(A2,365),30)&" day(s). ). ) ) ) )"
Here is the result we get when the formula is applied to all cells in the column:
Explanation of the formula
This formula is almost similar to the formula we used in the previous section.
You can choose to add themYES statement, but we chose not to add it to keep things simple.
The formula basically combines 3 terms with concatenation operators (&):
- The first term is INT(A2/365)&”year(s)”. This divides the number of days by 365, truncating the decimal part and keeping only the integer part. Finally, concatenates the string "Years)' at the end of the formula result. So if the number of days is 1002, this formula returns:
INT(1002/365) Ano(s)
=INT(2,745205479) Ano(s)
=2 what(s)
- The second term is INT(MOD(A2,365)/30)&”month(s)”. This gives us the remaining number of months. OMOD(A2,365)finds the remainder of dividing the number of days by 365. However, this value can be greater than 12 (months). Then divide that result by 30, since there are 30 days in a month on average. The INT function then truncates the decimal part of the result, keeping only the integer part. Finally, concatenates the string "Fun)"for the result If the number of days is 1002, this formula returns:
INT(MOD(1002,365)/30) Medida(s)
=INT(272/30) I(s)
=INT(9.066666667) I(s)
=9 month(s)
- The third term is MOD(MOD(A2,365),30)&”Day(s)”. This gives us the remaining number of days. OMOD(A2,365)Find the remainder you get by dividing the number of days by 365. This remainder indicates the number of months remaining. The external MOD function finds the remainder obtained when dividing this number of months by 30. The remainder obtained is the number of days remaining. Finally, the formula concatenates the string "Dias)“ to the result. So if the number of days is 1002, this formula returns:
MOD(MOD(1002,365),30) Foil(s)
=MOD(272,30) He(s)
=2 He(s)
Summarizing also means 1002 days2 years, 9 months and 2 days.
Convert years to days in Excel
Now let's look at how to do the opposite: how to convert the number of days to years.
This is really quite simple as we need to use the same concept of 1 year = 365 days again.
So the formula will be:
=A2*365
Convert the difference between two dates to years
Most Excel users usually need to find the number of years between two dates. This is often the main reason they want to convert days to years.
You may know that the difference between two dates can be found by simple subtraction, so you could have done that and gotten the number ofdays between the two dates.
If you landed on this page and were looking for a way to convert the number of days (after deduction) to years, we have an easier and faster solution for you.
Excel has a special function that is specifically designed to find out the number of days, months, or years between two specific dates. The function is the DATEDIF function.
The DATEDIF function syntax is:
SIFECHA(Start Date End Date,Unit)
Here,
- Start dateis the start date to be considered in the calculation. It can be a DATE value or a reference to a date value.
- data finalis the end date to be considered in the calculation. This can also be a DATE value or a reference to a date value.
- Unitis a string representing a unit of time. So if you want to find the number of years between theStart datejdata final, the units string can be specified as„J“.
Use:ÖUnitThe parameter can be one of the following:
- "Y" - to indicate the number of years
- "M" - to show the number of months
- "D" - to show the number of days
- "MD": Displays the number of days after subtracting whole months
- "YM" - to show the number of months after subtracting whole years
- "YD": Displays the number of days after subtracting whole years
Let's look at some examples to understand how you can use the DATEDIF function. Below is a range of start and end dates:
to find themnumberdiambetween the two appointments, you can use the DATEDIF formula as shown below (assuming you edit the entries in cells A2 and B2):
=SIFECHA(A2,B2,”D”)
Likewise to find the number ofyear oldBetween the two dates use:
=SIFECHA(A2,B2,”Y”)
To find the numberyears and daysBetween the two dates use:
= SIFECHA(A2,B2,”Y”)&” Año(s) “&SIFECHA(A2,B2,”YD”)&” Dia(s)”
To find the numberyears and monthsBetween the two dates use:
= SIFECHA(A2,B2,„Y“)&“ Año(s) „&SIFECHA(A2,B2,„YM“)& „Mes(es)“
To find the numberyears, months and daysbetween the two dates:
= SIFECHA(A2,B2,”Y”)&” Año(s) “&SIFECHA(A2,B2”,”YM”)&” Mes(es) “&SIFECHA(A2,B2”,”MD”)&” Día (s)”
Note that the DATEDIF method gives more accurate results than methods using MOD and INT (shown at the beginning of this tutorial).
This is because the DATEDIF function automatically takes into account the 30 and 31 day months (which the previous methods didn't take into account in order not to make the formulas too complex).
In this tutorial, we will show you some quick tricksConvert days to years excel.
In case you are looking for a way to find the number of yearsbetween two appointments, we also show you how to use the DATEDIF function to do this quickly. We hope this was helpful.
Other Excel tutorials that you may also find helpful:
- How to convert date to day of the week in Excel
- How to add days to a date in Excel
- How to Autofill Dates in Excel (Autofill Months/Years)
- How to convert month number to month name in Excel
- How to convert date to month and year in Excel
- Convert Military Time to Standard Time in Excel (Formulas)
- How to convert month name to a number in Excel?
- Find the date of the last Monday of the month in Excel
- Calculate days between two dates in Excel (weekdays/weekends)
FAQs
How can you convert days into years in Excel? ›
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.
What is the Excel formula for years? ›The Excel YEAR function is very straightforward and you will hardly run into any difficulties when using it in your date calculations: =YEAR(A2) - returns the year of a date in cell A2. =YEAR("20-May-2015") - returns the year of the specified date.
How do I calculate day and year in Excel? ›For example, if you want to calculate the day of the year for December 31st, 2016, you would enter =DATE(2016, 12, 31) . This will return 365, which is the day of the year for December 31st. You can also use the DATE function to return the day of the year for a given date.
How do I create a formula in Excel for days? ›=DAYS (end_date, start_date)
The function requires two arguments: Start_date and End_date. They are the two dates between which we wish to calculate the number of days.
So the basic formula to convert days into years is to divide the number of days by number(365).
How do I change the date format to year only in Excel? ›Select the cells you want to format. Press CTRL+1. In the Format Cells box, click the Number tab. In the Category list, click Date, and then choose a date format you want in Type.
How do I calculate year in dd mm yyyy in Excel? ›First, we must enter the formula =TEXT(A1, “yyyy-mm-dd”) in cell B1. b. Then, press the “Enter” key. The date in cell A1 (22/1/2019) is converted to 2019-01-22 in cell B1.
How do you convert mm/dd/yyyy to year in Excel? ›- Select the dates you want to convert to yyyymmdd, and right click to show context menu, and choose Format Cells.
- In the Format Cells dialog, under Number tab, select Custom from Category list, and type yyyymmdd into the textbox of Type in right section. ...
- Click OK.
The Excel DAY function returns the day of the month as a number between 1 to 31 from a given date. You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value into another function, like the DATE function.
How do I calculate 365 days in Excel? ›=”A3+365” returns December 15, 2014, which is 365 days after December 15, 2013. Note that the number that you add or subtract is always the number of days. You cannot substitute months or years. Similarly, you can subtract two dates to get the number of days between them.
How do you convert days to years including leap years? ›
365 days in a year. 366 days in leap years. Leap years comes every 4th year. 365*4 = 1460 days in 4 years but with leap year +1 it becomes 1461 days in 4 years.
How many days are we into a year? ›Day 59. Day of the year is a number between 1 and 365 (in 2023), January 1 is day 1. After today 306 days are remaining in this year.
How do I change the date format in Excel using formulas? ›- Select the dates whose format your want to change, or empty cells where you want to insert dates.
- Press Ctrl+1 to open the Format Cells dialog. ...
- In the Format Cells window, switch to the Number tab, and select Date in the Category list.
- Under Type, pick a desired date format.
- Enter =DATEVALUE(
- Click the cell that contains the text-formatted date that you want to convert.
- Enter )
- Press ENTER, and the DATEVALUE function returns the serial number of the date that is represented by the text date. What is an Excel serial number?
What is the YEAR Function? The YEAR Function[1] is an Excel Date/Time function that is used for calculating the year number from a given date. The function will return an integer that is a four-digit year corresponding to a specified date.
How do I convert numbers to dates in Excel? ›On the Home tab, in the Number group, click the Dialog Box Launcher next to Number. You can also press CTRL+1 to open the Format Cells dialog box. In the Category list, click Date or Time.
How do I convert a date to month and year in Excel? ›- Formula 1: Convert Date to Month and Full Year (e.g. 01/2022) =TEXT(A1, "mm/yyyy")
- Formula 2: Convert Date to Month and Last Two Digits of Year (e.g. 01/22) =TEXT(A1, "mm/yy")
Description. Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.
How do you make 365 days in a year? ›The Short Answer: It takes approximately 365.25 days for Earth to orbit the Sun — a solar year. We usually round the days in a calendar year to 365. To make up for the missing partial day, we add one day to our calendar approximately every four years.
Can Excel calculate days from date? ›What'll you need to do is subtract today's date from the project start date. Excel has a TODAY function built in, which will save you from typing in the date in the correct format and continually updating as the days change. So, the formula is: =TODAY() – B2.
How do I convert two dates to years in Excel? ›
In a new cell, type in =DATEDIF(A1,B1,”Y”). The “Y” signifies that you'd like the information reported in years. This will give you the number of years between the two dates. To find the number of months or days between two dates, type into a new cell: =DATEDIF(A1,B1,”M”) for months or =DATEDIF(A1,B1,”D”) for days.
How many days in a year Excel? ›There are 365 or 366 (leap year) days in a year. And in this tutorial, you will learn to get the day number in the year for a date.
How do you convert days into days months and years? ›- Take the number of days as input.
- For the number of years, divide the input by 365 and obtain its quotient.
- For the number of weeks, divide the input by 365 and obtain its remainder. ...
- For the number of days, divide the input by 365 and obtain its remainder.
=”A3+365” returns December 15, 2014, which is 365 days after December 15, 2013. Note that the number that you add or subtract is always the number of days. You cannot substitute months or years. Similarly, you can subtract two dates to get the number of days between them.
How do I extract month and year from dd mm yyyy in Excel? ›- Select the date , left click on that cell.
- And select the format cell.
- Select the custom option.
- And type mmmm.
- This will convert the date into the respective month.
Convert text dates by using the DATEVALUE function
Select a blank cell and verify that its number format is General. Click the cell that contains the text-formatted date that you want to convert. Press ENTER, and the DATEVALUE function returns the serial number of the date that is represented by the text date.
The DATE function takes three arguments: year, month, and day. For example, if you want to change the year for a date from 2015 to 2016, you would use the following formula: =DATE(2016,1,1). This would change the year for that date to 2016.
How do I calculate age in YYYY MM DD in Excel? ›- =DATEDIF(B2,TODAY(),"Y")
- DATEDIF is a function that calculates the difference between two dates. ...
- If you'd rather see the age in days or months, use "D" or "M" instead.
The YEAR Function[1] is an Excel Date/Time function that is used for calculating the year number from a given date. The function will return an integer that is a four-digit year corresponding to a specified date. For example, if we use this function on a date such as 12/12/2017, it will return 2017.