How to Convert Days to Years in Excel

Excel stores durations as a number of days, so sooner or later you’ll want to show something like 1,000 days as years instead. There’s no single CONVERT option for days to years, but a simple division handles most cases, and a couple of functions take care of the rest.

This guide covers the basic formula, whole years, a years-months-days breakdown, and converting the gap between two dates directly.

How to Convert Days to Years in Excel

Step 1: Enter your days

Type the number of days in a column, for example in cell A2. If your days come from subtracting two dates, that works too. Just make sure the cell is formatted as a number, not a date.

Step 2: Divide by the length of a year

In the next column, enter =A2/365.25 and press Enter. Using 365.25 averages in leap years, which is the standard approach for ages and long durations. If you want a simple 365-day year, use =A2/365 instead.

Step 3: Fill the formula down

Double-click the fill handle (the small square at the bottom-right of the cell) to copy the formula to the rest of your rows.

Step 4: Control the decimals

Use Home > Decrease Decimal to show fewer decimal places, or wrap the formula in ROUND so the stored value changes too: =ROUND(A2/365.25,1).

Here’s how the formulas compare:

Days (A)	Formula	Result
365	=A2/365	1.00
1000	=A3/365.25	2.74
1000	=INT(A4/365.25)	2
1000	=ROUND(A5/365.25,1)	2.7

Show Whole Years Only

To drop the fraction, use =INT(A2/365.25). INT always rounds down, so 729 days shows as 1 year, not 2. That matches how ages and years of service are normally counted. If you want normal rounding instead, use =ROUND(A2/365.25,0).

Convert Days to Years, Months and Days

For a readable result like “2 years, 8 months, 26 days,” you can use DATEDIF, which treats your number of days as a date offset:

=DATEDIF(0,A2,"y")&" years, "&DATEDIF(0,A2,"ym")&" months, "&DATEDIF(0,A2,"md")&" days"

DATEDIF isn’t listed in Excel’s function autocomplete, but it works in all current versions. Because months have different lengths, this result is an approximation. If you prefer a fixed 30-day month, use plain arithmetic instead:

• Years: =INT(A2/365)
• Months: =INT(MOD(A2,365)/30)
• Days: =MOD(MOD(A2,365),30)

Convert the Days Between Two Dates to Years

If you have a start date in A2 and an end date in B2, you don’t need to calculate days first:

• =YEARFRAC(A2,B2) returns the years between the dates as a decimal, such as 2.74. Add a third argument of 1 (=YEARFRAC(A2,B2,1)) to use actual day counts, which is more accurate across leap years.
• =DATEDIF(A2,B2,"y") returns complete years only, which is ideal for ages.
• =(B2-A2)/365.25 gives the same kind of result as the basic method.

Microsoft explains the day-count options in its documentation for the YEARFRAC function.

Troubleshooting

• The result shows as a date. Excel copied date formatting into the cell. Select it and choose Number or General from the Number Format box on the Home tab.
• #VALUE! error. Your days are stored as text. Look for a green triangle in the cell, click the warning icon and choose Convert to Number.
• #NUM! from DATEDIF. The start date is later than the end date. Swap them, or wrap the formula in IFERROR.
• Results are slightly off from an online calculator. Calculators may use 365 or 365.2425 days per year. Pick one divisor and use it consistently.
• Negative numbers. INT rounds negative values away from zero. Use TRUNC instead if your days can be negative.

Tips

• Put the divisor (365 or 365.25) in its own cell and reference it with an absolute reference, like =A2/$D$1, so you can change it in one place.
• Label the result column with the unit, such as “Years,” so readers know what the number means.
• For months, divide by 30.44 (the average month length) or use =DATEDIF(A2,B2,"m") between two dates.
• Related conversions work the same way. See how to convert minutes to hours in Excel and calculate minutes between two times in Excel.

Frequently Asked Questions

Should I divide by 365 or 365.25?

Use 365.25 for long periods like ages or tenure, because it accounts for leap years. Use 365 if your organization defines a year as 365 days, such as for some financial calculations.

Is there a built-in function to convert days to years?

Not for a plain number of days. CONVERT can change “day” to “yr” with =CONVERT(A2,"day","yr"), which uses 365.25 days. Between two dates, YEARFRAC and DATEDIF do the job directly.

How do I convert years back to days?

Multiply instead of divide: =A2*365.25.

How do I convert months to years?

Divide the number of months by 12, for example =A2/12.

Does this work in Google Sheets?

Yes. Division, INT, ROUND, YEARFRAC and DATEDIF all work the same way in Google Sheets.

Wrapping Up

Divide by 365.25 for a decimal number of years, wrap it in INT for whole years, and use DATEDIF when you need years, months and days. If you’re starting from two dates, YEARFRAC gives you the answer in one step.

Related: how to subtract two dates in Excel.