What is the YEAR Function?

Using days in jae won modelingWhat is gaue won ModelingFinancial modeling is performed in Excel to estimate a company"s financial performance. Synopsis of what is jae won modeling, exactly how & why to construct a model. Is very common and also the YEAR role helps extract a year number native a day into a cell. The role can also be provided to extract and also feed a year worth into an additional formula such as the date function.

You are watching: When you enter a two digit year in excel



The serial_number dispute is required. It is the day of the year that us wish come find. Dates should be gotten in either by using the DATE role or as outcomes of various other formulas or functions. Difficulties can take place if days are entered as text.

How to usage the YEAR duty in Excel?

It is a built-in function that can be supplied as a worksheet duty in Excel. To recognize the uses of the YEAR function, let’s consider a few examples:

Example 1

Let’s i think we mutual an Excel file with several users. We desire to ensure the a user beginning only days in a particular year. In such a scenario, we can use data validation through a practice formula based on the YEAR function.

Suppose we are provided the following data:


Using the data below, we want that user requirements to get in only days in B4 and also B5. For this, us will set a data validation dominion as follows:


Data validation rules will be triggered when any kind of user tries to addor change the cell value.

When the year match, the expression returns TRUE and also the validation succeeds. If the year don’t match, or if the YEAR role is no able to extract a year, the validation will fail.

The practice validation formula will certainly simply examine the year of any kind of date versus a hard-coded year value utilizing the YEAR duty and return one error if the year is not entered. If we desire to make sure that a user enters just a date in the existing year then the formula come be offered is =YEAR(C5)=YEAR(TODAY()).

The TODAY function will return the existing date top top an recurring basis, therefore the formula return TRUE only as soon as a date is in the existing year.

Example 2

If us want, we can extract the year indigenous a given set of dates. Expect we great to extract Year indigenous the days below:


Using the YEAR formula, we can extract the year. The formula to use is:


We will get the result below:


Things come remember about the YEAR Function

Dates have to be provided to Excel functions as either:Serial numbers; orReference to cells containing dates; orDate values returned from other Excel formulas.

If we shot to input days as text, then there is a threat that Excel may misinterpret them, as result of the different date systems or date interpretation setups on her computer.

2. The value changed by the YEAR role will be Gregorian worths regardless the the display format for the supplied date value.

3. The YEAR role perfectly understands dates in all feasible formats.

See more: Where Is Bleach On The Ph Scale, What Color Does Ph Paper Turn With Bleach

4. If we wish come see only the year for dates stored, we can style the cells accordingly. In this case, no formula is needed. We can just open the layout Cells dialog by pressing Ctrl + 1. After that select the Custom classification on the Number tab, and also enter one of the codes below in the type box:

yy – to display 2-digit years, together 00-99.yyyy – to display 4-digit years, as 1900-9999.


Click here to download the sample Excel file

Additional resources

Thanks for analysis CFI’s overview to crucial Excel functions! through taking the time to learn and also master this functions, you’ll considerably speed up her financial modeling. To find out more, examine out these added CFI resources: