How To Calculate Year, Month and Day In A Given Number

Let me show you how to convert the number of days to year/month/day in the prescribed format “1 year 4 months 25days” in Excel.

Convert number of Days to year/month/day format

For this, we need to use the DATEDIF function.

Syntax for DATEDIF Function:

DATEDIF(start_date,end_date,unit)

First of all select a blank cell you where we want the output of the conversion.

Type the formula as shown below:

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

Here A2 is the cell containing number of days.

See the image below:

Press Enter and excel will generate the result.

Just double click the fill handle and it will automatically copy the same formula for the range. And then you will see all numbers are converted to year/month/day.

See image below: