How to convert months to years and months in Excel?

In Excel worksheet, have you ever tried to convert a value in months into a format of years and months? For instance, if you have 56 months, you might want to display it as 4 years and 8 months, as shown in the image on the left. Converting months to a more readable years-and-months format enhances clarity, especially when reporting durations or tenure. In this article, we will explore practical methods to accomplish this task in Excel, and discuss in which scenarios each approach is most useful.

Convert months to years and months with formula

Convert months to years and months with VBA Macro

Convert months to years and months with formula

When you want to transform a numeric value representing months into a more understandable period expressed in years and months, Excel formulas can efficiently achieve this. This method is especially handy for day-to-day tasks, small datasets, or when you wish to avoid any use of code. Formulas are dynamic, so they automatically update the result if the input months change.

To convert months to years and months directly with a formula, you can use the following approach. Note that in this solution, full years are calculated by dividing the total months by 12, while the remainder gives you the remaining months:

Copy and paste the following formula into a blank cell:

After you enter this formula, press Enter to see the years and months conversion. To apply the formula to additional rows, drag the fill handle down alongside your list. This technique ensures that every value is instantly converted, enabling bulk processing with ease.

Tips and precautions:

• If the number of months is less than 12, the formula will show "0 years and X months".
• Negative values and non-integer values may cause unexpected results; ensure source values are valid and formatted as numbers.
• To show blank when there is no input, you can use an IF function to avoid displaying "0 years and 0 months".

Common troubleshooting:

• If you see a #VALUE! error, check that the cell referenced contains a valid numeric value.
• If you wish to customize the output format, adjust the text within quotes in the formula as needed.

This formula method is suitable for dynamic conversion where the values may change, and for users less familiar with programming or macros. For very large datasets or frequent, repetitive conversions, an automated approach may be more efficient. See below for a macro-based solution.

Convert months to years and months with VBA Macro

If you regularly need to convert lists of months to the years-and-months format, or if you are working with very large datasets, Excel's VBA macro feature can automate this task, saving time and reducing manual error. Using a macro is especially valuable when you want to process multiple columns or apply the logic to new data as part of a repeatable workflow.

1. Open the VBA editor by clicking Developer > Visual Basic. If you don't see the Developer tab, you can enable it via Excel Options > Customize Ribbon. Once the editor opens, click Insert > Module. In the module window that appears, copy and paste the following code:

Sub ConvertMonthsToYearsMonths()
    Dim rng As Range
    Dim cell As Range
    Dim years As Integer
    Dim months As Integer
    Dim outputCol As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set rng = Application.InputBox("Select the range of months to convert", xTitleId, Selection.Address, Type:=8)
    
    If rng Is Nothing Then Exit Sub
    
    outputCol = rng.Columns(rng.Columns.Count).Column + 1
    
    For Each cell In rng
        If IsNumeric(cell.Value) And cell.Value >= 0 Then
            years = Int(cell.Value / 12)
            months = cell.Value Mod 12
            cell.Offset(0, rng.Columns.Count).Value = years & " years and " & months & " months"
        Else
            cell.Offset(0, rng.Columns.Count).Value = ""
        End If
    Next
End Sub

2. To run the macro, click the button, or press F5. A dialog box will prompt you to select the range of months you wish to convert (for example, A2:A20). Select your range and click OK.

The macro will automatically write the result (in "X years and Y months" format) to the column immediately to the right of your selection. For example, if you selected A2:A20, the results will be placed in B2:B20.

Usage notes and tips:

• Ensure your selection contains valid positive numbers representing months. If a cell is empty, it will be treated as zero and return "0 years and 0 months". Non-numeric or negative values may result in blank or error outputs.
• The macro outputs results in the column directly to the right of the selected range. Check that column before running the macro to avoid overwriting data.
• You can run this macro multiple times, making it convenient for batch processing or new incoming data.
• Undo (Ctrl+Z) is not available for VBA actions, so always keep backup copies of your important data before running macros.
• If your data includes negative or fractional months, these will be treated as blanks (see the VBA conditions; modify as needed).

This VBA approach is practical for users who need to handle repeated conversions, process many rows at once, or automate productivity tasks. With a single click, you can convert entire datasets and streamline your workflow. However, if you’re new to VBA, ensure you enable macros, and consult with your IT administrator if you have macro restrictions in your environment.

Troubleshooting:

• If you receive an error on running the macro, confirm the Developer tab is enabled and macros are permitted in your Excel settings.
• If the results overwrite existing data, ensure the adjacent column to your data is empty before running the macro.
• Should you accidentally select the wrong range, simply rerun the macro and select again.

In summary, both the formula and VBA solutions make converting months into a readable years-and-months format simple and efficient. Choose the method that best suits your dataset size and workflow preference. For additional automation, you can further customize the VBA code to suit special formatting or output needs.

More relative articles:

---

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

Read More... Full Features 30-Day Free Trial... Purchase... 

---

Office Tab - brings tabbed interface to Office, and make your work much easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Full Features 30-Day Free Trial... Purchase...