This method will introduce a formula to convert the number of days to year/month/day such as "1 year 4 months 25days" in Excel. Please do as follows:

1. Select a blank cell you will output the conversion result, and enter the following formula into it, and press the Enter key.

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

Note: A2 is the cell with number of days that you will convert to year/month/day.

2. Keep selecting the cell of conversion result, and drag its Fill Handle to the range as you need. And then you will see all numbers are converted to year/month/day. See screenshot:

Some users may feel hard to memorize formulas to convert the number of days to year/month/days in Excel. Here I recommend the Formula Helper of Kutools for Excel to quickly finish the conversation without memorizing the painful formulas.

1. Select the cell you will put the calculation result at, and click Kutools > Formula Helper > Formula Helper to enable the feature.

2. In the Formulas Helper dialog, please select Date from the Formula Type drop-down list, click to highlight Convert days to year month day in the Choose a formula list box, then specify the number cell in the Number box, and finally click the Ok button.

Now the calculation result is output in the selected cell. If necessary, you can drag the AutoFill Handle of this cell to copy this formula to other cells.

Sometimes, you type or paste dates into Excel, but the dates show as 5-digits of general numbers. In this case, you can easily convert 5-digits of general numbers to dates with following methods:

Select the 5-digits numbers, and click Home > Number Format box > Short Date. And then you will see all selected 5-digits numbers are converted to dates with default date formatting at once.

Kutools for Excel's Apply Date Formatting utility can help you quickly convert 5-digits numbers to dates with custom date formatting.

1. Select the 5-digits of numbers, and click Kutools > Format > Apply Date Formatting.

2. In the opening Apply Date Formatting dialog box, select the specified date formatting you will use in the Date formatting box, and click the Ok button. And then you will see all numbers are converted to dates with specified date formatting. See screenshot:

Sometimes, you may get some special numbers of yyyymmdd, such as 20151215. This section will show you an easy way with one click to convert these numbers of yyyymmdd to normal dates by the Convert to Date utility of Kutools for Excel.

Select the cells with special numbers of yyyymmdd, and click Kutools > Content > Convert to Date. See screenshot:

And then you will see all selected numbers are concerted to normal dates. See screenshot:

Please click the Close button to close the Convert to Date dialog box.

How to convert date to weekday name or month name in Excel?

How to add / subtract days / months / years to date in Excel?

How to count / calculate weeks / months / years between two dates in Excel?