Format Excel Cells as Years and Months with Custom Number Formats
Question details
The user wants manually entered numeric values to display with fixed year or month labels in spreadsheet cells.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is entering duration values and wants the displayed number to include labels such as years and months.
- Observed behavior
- The goal is to show numeric inputs in a readable format, such as 3 yrs or 6 mos, while retaining the underlying numeric value.
Decide whether years and months will be entered in separate cells or as one calculated duration. A custom number format adds display text only; it does not convert a number into a true date or duration calculation.
Add year or month labels with a custom number format
Use this method when each cell contains one manually entered number that should display as years or months.
Custom number formats preserve the numeric value for calculations while adding fixed text to its display. For example, a value of 3 can display as 3 yrs.
Select the Excel cells that contain the numbers you want to label, such as cells containing 3, 6, or 12.
Right-click the selected cells and choose Format Cells, or press Ctrl+1.
On the Number tab, select Custom from the Category list.
In the Type box, enter 0 "yrs" for year values or 0 "mos" for month values, then select OK.
Use separate cells for years and months
Use this approach when years and months are entered independently, such as 2 years and 5 months.
Add Year and Month Labels in WPS Spreadsheets
WPS Spreadsheets lets you apply custom number formats to manually entered duration values, so numbers can display with year or month labels while remaining available for calculations.
- 1. Select the duration cells: Open the worksheet in WPS Spreadsheets and select the cells containing year or month values.
- 2. Open Format Cells: Right-click the selection and choose Format Cells, or press Ctrl+1.
- 3. Select the Custom category: Choose Number, then select Custom in the category list.
- 4. Apply a year or month format: Enter 0 "yrs" for years or 0 "mos" for months in the format code field.
- 5. Confirm the display: Select OK and verify that values such as 3 display as 3 yrs or 3 mos.
Frequently Asked Questions
What custom format displays 3 as 3 yrs in Excel?
Select the cell, press Ctrl+1, choose Custom, and enter 0 "yrs" in the Type box. The numeric value 3 will display as 3 yrs.
How do I display a number as months instead of years?
Use the custom number format 0 "mos". For example, entering 6 displays as 6 mos while the underlying value remains 6.
Can one custom format show both years and months from a single number?
Not reliably without a defined conversion rule. If years and months are separate values, use separate cells with 0 "yrs" and 0 "mos", or calculate a combined text result with a formula.
Will adding yrs or mos with a custom format affect formulas?
No. The quoted text is display-only, so formulas continue to use the underlying number. For example, a cell displaying 3 yrs still evaluates as 3.
Why should I put yrs and mos in quotation marks in a custom format?
Quotation marks tell Excel that yrs and mos are literal text to display. Without quotation marks, Excel may interpret letters in the format code instead of showing the label as intended.
