Year from a text date in Excel: YEAR and DATEVALUE

An export contains a date stored as text, and your report needs its year. First interpret the date, then retrieve the year. In Excel, DATEVALUE and YEAR perform those steps. The old Polish name “WARTOŚĆ.ROK” is not an Excel function and should not be translated into an invented English equivalent.

I will distinguish an actual Excel date from a date stored as text. The distinction matters because changing the display format does not convert arbitrary text into a date. These synthetic examples state the regional assumptions used to interpret the text.

When the cell already contains a date

Excel stores dates as serial numbers. YEAR retrieves the year from such a value and returns it as a number. If A2 contains November 9, 2026, use:

=YEAR(A2)

The result is 2026. To prepare the example without ambiguity while entering the date, put =DATE(2026,11,9) in A2. This specifies each part explicitly.

Entering the ordinary number 2026 into A2 does not mean “the year 2026” to YEAR. The function treats it as a date serial number. This can cause confusing results when a column already contains year numbers and somebody applies YEAR to them again.

When the date is text

DATEVALUE converts recognized date text into a date serial number. It does not return the year by itself. YEAR then retrieves the year from that conversion result.

Assume English United States regional settings that recognize August 22, 2011. Store this as text in A2, for example by entering a leading apostrophe. The apostrophe forces text entry and is not part of the date value.

=YEAR(DATEVALUE(A2))

DATEVALUE recognizes August 22, 2011, and YEAR returns 2011. In Polish Excel, the corresponding formula is =ROK(DATA.WARTOŚĆ(A2)). With Polish settings recognizing day-month-year text, the example input can be 22.08.2011.

Do not assume that every representation works identically on every computer. Microsoft documents the role of date settings in interpretation. These English examples use commas; argument separators are also affected by configuration.

Keep three situations separate

Left or right alignment can be a clue, but somebody may have changed it manually. Inspect the value type rather than only its appearance. ISNUMBER can establish that a cell contains a number; it does not prove that the number represents a meaningful date in the business process.

Regional settings and ambiguous input

Text 03/04/2026 can mean March 4 or April 3. Both interpretations produce year 2026. A correct year therefore does not prove that the full date was parsed correctly. The problem may only become visible in a later monthly report.

If you control the export, agree on an unambiguous format and an import method. For a larger dataset, I prefer converting the whole column under specified regional settings while preserving the original strings for comparison. I avoid adding a separate ad hoc formula for every variation without knowing what the source intended.

Applying a Date display format does not perform that import. Formatting controls how Excel displays a value. It does not define a universal text parser. After conversion, inspect several complete dates, including examples whose day exceeds 12, and compare them with the source.

Missing values, errors and incomplete dates

Unrecognized text can produce #VALUE!, called #ARG! in Polish Excel. An invalid function name can produce #NAME?. Those are different problems: one concerns interpreting a value, the other recognizing the formula. Microsoft’s function catalog lists the documented functions.

I do not automatically treat a blank cell as a date. If blanks are permitted, check for them before conversion. For a column intended to contain text dates only, one option is:

=IF(A2="","",YEAR(DATEVALUE(A2)))

Nonempty invalid strings still need investigation. I do not replace every failure with year zero. That could become a misleading report category and conceal an import problem.

DATEVALUE may supply the current year when recognized input omits it. For historical data, I therefore require an explicit year in the source. A result that depends on the computer’s current date is a poor foundation for a repeatable report.

Displaying a year versus creating a year field

If the aim is only to show a year instead of a full date, a display format can do that while leaving the full date value in place. A helper column using YEAR creates a separate numeric field suitable for grouping and comparisons.

Do not overwrite the original date with its year if later work needs months or event ordering. Keeping only 2026 discards the day and month. I preserve the original date and add a clearly labeled year column alongside it.

Exercise with two input types

Construct November 9, 2026 with DATE in A2 and retrieve its year in B2. In A3, enter a full date as text using a format your regional settings recognize. In B3, convert it and retrieve the year. Compare the complete converted date with its original string as well.

Finally, add a blank cell and invalid text such as “date missing.” Decide what status each case should receive. For dates assembled from separate parts, read the DATE entry. For complete elapsed periods, see DATEDIF.