This post will guide you how to convert a given day numbers into Years, Months, Days using a formula or a User Defined function with VBA code in Excel 2013/2016.2019/365. How do I convert days to Years, Months, Days with a formula in Excel. How to convert a general numbers to a standard date in Excel.

1. Convert Days to Years, Months, Days using Formula

If you want to convert a given day numbers to Years, Months, Days in your worksheet, you can use an Excel formula based on the DATEDIF function. Like this:

=DATEDIF(0,B1,"y") & " Years " & DATEDIF(0,B1,"ym") &  " Months " & DATEDIF(0,B1,"md") & " Days "

You need to type this formula into a blank cell and press Enter key on your keyboard, and then drag the AutoFill Handle over to other cells to apply this formula.

You should see that all days have been converted to Years, Months, Days format in your worksheet.

2. Convert Days to Years, Months, Days using User Defined Function with VBA Code

You can convert the given number of days in a cell to years, months and days and returns the results as a string using a User Defined Function with VBA code. Just do the following steps:

Step1: Press ALT + F11 to open the VBA editor.

Step2: In the VBA editor, insert a new module by clicking “Insert” > “Module” in the menu bar.

Step3: Copy and paste the below code into the new module.

Function ConvertDaysToYMD_ExcelHow(days As Long) As String
    Dim years As Long
    Dim months As Long
    Dim remainder As Long
    
    ' Calculate the years, months, and remaining days
    years = Int(days / 365)
    remainder = days Mod 365
    months = Int(remainder / 30)
    remainder = remainder Mod 30
    
    ' Format the result as a string
    ConvertDaysToYMD_ExcelHow = years & " years, " & months & " months, " & remainder & " days"
End Function

Step4: Return to Excel and enter a formula in a cell that references the ConvertDaysToYMD_ExcelHow function.

=ConvertDaysToYMD_ExcelHow(B1)

Step5: Press Enter to calculate the formula, and the result will be displayed as a string in the cell.

Note that this code assumes that a year has 365 days and a month has 30 days. This may not be accurate for all cases, so you may need to adjust the code accordingly.

3. Convert General Number to Standard Date

Step1: select cells that contain 5-digits numbers

Step2: go to HOME tab, click Number Format box, and then select Short Date from the drop down menu list.

You would notice that all selected numbers have been converted as a standard date format.

4. Video: Convert Days to Years, Months, Days in Excel

This video will show you how to convert days to years, months, and days in Excel using a formula or VBA code.

5. Related Functions

• Excel DATEDIF function
  The Excel DATEDIF function returns the number of days, months, or years between tow dates.The syntax of the DATEDIF function is as below:=DATEDIF (start_date,end_date,unit)…