Excel Guides

Displaying a Number as Years and Months in Excel

To display a number as years and months in Excel, you can use the TEXT function. The TEXT function can be used to format a number as text, which can then be used to display the number as years and months. For example, if you have a number in cell A1, you can use the following formula to display the number as years and months:

=TEXT(A1,"0 yyyy") & "-" & TEXT(A1,"mm")

This formula will take the number in cell A1 and format it as text using the "0 yyyy" format, which will display the number of years. The formula will then concatenate a hyphen (-) and the month from cell A1, which will be displayed as mm. This will result in the number being displayed as years and months.

If you want to display the number of days as well, you can use the following formula:

=TEXT(A1,"0 yyyy") & "-" & TEXT(A1,"mm") & "-" & TEXT(A1,"dd")

This formula will take the number in cell A1 and format it as text using the "0 yyyy" format, which will display the number of years. The formula will then concatenate a hyphen (-) and the month from cell A1, which will be displayed as mm. The formula will then concatenate another hyphen (-) and the day from cell A1, which will be displayed as dd. This will result in the number being displayed as years, months, and days.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.