Function Problems

How to Display DATEDIF Results as Years and Months in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to calculate the time between two dates and display the result in a specific years-and-months format (e.g., 1.08 for 1 year and 8 months) rather than a standard mathematical decimal fraction.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Calculating exact age, tenure, or project duration by displaying completed years and remaining months as a precise decimal representation.
Observed behavior: Standard division of total months by 12 produces standard fractional decimal values (like 1.7) instead of cleanly separating years and months into a custom decimal format (like 1.08).

Before you start

Ensure your start and end dates are correctly formatted as date values in your spreadsheet, and note the cell references containing these dates before building your formula.

Solution 1Recommended

Use INT and MOD functions with DATEDIF

Calculate the completed years using the INT function and append the remaining months as a two-digit decimal using the MOD function.

By nesting DATEDIF inside the INT and MOD functions, you can extract the exact number of years and map the leftover months directly into the decimal place. This ensures that 1 year and 8 months formats exactly as 1.08 instead of mathematically dividing into 1.67.

Identify your date cells

Determine the cells containing your start date (e.g., cell D13) and your end date or current date (e.g., cell $A$1).

Enter the formula

Click on the cell where you want the result to appear and type the formula: =INT(DATEDIF(D13,$A$1,"m")/12)+(MOD(DATEDIF(D13,$A$1,"m"),12)/100)

Apply cell formatting

Press Enter to apply the formula. To ensure single-digit months display correctly with a leading zero (e.g., .08 for 8 months), right-click the cell, select Format Cells, choose Number, and set decimal places to 2.

Formula Breakdown: The INT section calculates full years by dividing total months by 12 and rounding down. The MOD section calculates the remainder of months after dividing by 12, then divides that remainder by 100 to place it neatly after the decimal point.

Solution 2

Alternative: Concatenate Years and Months into a Text String

Use DATEDIF with the 'y' and 'ym' arguments to display the result as a highly readable text string instead of a decimal.

Process Data Effortlessly

Calculate Dates Easily with WPS Spreadsheet

WPS Spreadsheet fully supports the DATEDIF, INT, and MOD functions, allowing you to handle complex date calculations seamlessly. Enjoy a clean interface and powerful data analysis tools for free.

1. Open WPS Spreadsheet: Launch WPS Office on your computer and open your date calculation workbook.
2. Select the target cell: Click the cell where you want the age, tenure, or difference calculated.
3. Apply the DATEDIF formula: Paste your custom DATEDIF and MOD formula directly into the formula bar and press Enter to instantly see the results.

Full compatibility with Microsoft Excel formulas and file formats (.xlsx)Native support for hidden advanced date functions like DATEDIFLightweight application with fast calculation speedsBuilt-in formatting tools for customized numerical and date displays

microsoft office alternative - wps office

Frequently Asked Questions

Why does standard division by 12 not work for calculating years and months?

Dividing total months by 12 produces a true mathematical fraction (e.g., 20 months / 12 = 1.666). It does not separate the remainder as a distinct 'months' value, which is why 1 year and 8 months shows mathematically as 1.67 instead of a cleanly separated 1.08 format.

What does the 'm' parameter mean in the DATEDIF function?

The 'm' parameter tells the DATEDIF function to calculate the total number of complete months between the start date and the end date.

How do I calculate just the remaining months between dates?

You can use the DATEDIF function with the 'ym' parameter. For example, =DATEDIF(start_date, end_date, "ym") will return only the remaining months after subtracting the fully completed years.

Can I use the DATEDIF function in WPS Spreadsheet?

Yes, WPS Spreadsheet fully supports the DATEDIF function. It mirrors standard date calculation capabilities, allowing you to use the exact same formulas seamlessly.

Excel & Data Problems