Convert text to year in Excel

The easiest way to convert text to a year in Excel is with the TEXT function. It takes value as the first argument and formatting as the second.

TEXT(value, format_text)

You can use the following formula to convert the date in Cell A2 and display it as a year only.

=TEXT(A2,"yyyy")

You can also display the last two digits of the year by using this formula.

=TEXT(A2,"yyyy")

You can also use the text formula to display months, days, hours, minutes and seconds, and separate them with a dash, slash, or any other separator.

You can combine this date with text to make it more readable.

="The year is "&B2&"."

And with the CONCAT function.

=CONCAT("The year is ",B2,".")

Here’s a table with different formats.

FormatDefinition
yyyy / yyyA year with four digits (1998, 2019, etc.)
yy / yA year with two digits (98, 19, etc.)
mmA month with leading zero (05, 12, etc.)
mA month without leading zero (5, 12, etc.)
ddA day with leading zero (05, 12, 30, etc.)
dA day without leading zero (5, 12, 30, etc.)