EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips YEAR Formula in Excel
 

YEAR Formula in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 21, 2023

YEAR Formula in Excel

 

 

Introduction to YEAR Formula in Excel

Year Formula in Excel simply returns the year from the selected date cell. And the value we will get obviously in 4 digit year containing the century and year out of that century. The syntax of the Year function considers only the serial number, but we can even select the cell with the date, and this function will extract the same out of it. Feeding the 1 in-Year function will return 1900 as the first year available in MS Office.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax: 

year formula syntax

An argument in YEAR Formula

  • serial_number: Serial Number represents the date.

Explanation

If you are confused about how the serial number represents the date? Then you should understand how data save in Excel. From 1 Jan 1900, excel assigned a serial number to each date. For 1 Jan 1900 one, 2 Jan 1900 two and so on.

For example, if we give serial number 1 in the year formula, it will return 1900 per the logic below.

year formula 1

Observe the formula; we just input 1 in the serial number, and it returned 1900, the year related to that date. We will see a few more examples of how to use the YEAR function.

How to Use Excel YEAR Formula in Excel?

Excel YEAR Formula is very simple and easy. Let’s understand how to use the Excel YEAR Formula with a few examples.

You can download this YEAR Formula Excel Template here – YEAR Formula Excel Template

Example #1 – YEAR Formula with DATE Function

To know the 4-digit year, we need to input the date’s serial number. But, how can we every time calculate the serial number for a date every day? So, instead of using the serial number, we can use the date formula.

Follow the steps below to get the year from a date using the date formula instead of the serial number.

First, start the YEAR Formula as below.

year formula syntax

Input the DATE Formula in place of the serial number as below.

YEAR formula example 1-1

We must input the year, month, and day in the DATE Formula.

YEAR formula example 1-2

Press Enter Key.

YEAR formula example 1-3

As we have input the Year as 2019 in the date formula, it returns the year as 2019.

Example #2 – Year Formula with TODAY Function

If we want to get the current year details using the Today function, we can easily get it. Follow the below steps.

Start the Year Formula as below.

year example 2-1

We need to get today’s 4-digit year number, hence the input TODAY function.

year formula example 2-2

Press Enter after closing the TODAY function. It will return the current year number as below.

year formula example 2-3

Example #3 – Comparing Two Dates

They are comparing two dates, whether they belong to the Same Month and Year. Consider two sets of different dates like the below screenshot.

year example 3-1

Now with the help of the year and month function, we will check how many days in the same row are related to the same month and year.

Start the Formula with the YEAR first, as below.

year example 3-2

Select the First Date Cell from Set 1.

year example 3-3

Now Add & Symbol and add MONTH function for the same cell as below.

year example 3-4

Up to now, we have merged the year and month of the first date from set 1. Similarly, do for the first date from set 2 also.

year example 3-5

Now it will return if the month and year of both dates are matched. If the year or month anyone criteria is not matched, it will return False.

year example 3-6

Drag the same formula to the other cells.

year example 3-7

Example #4 – Find the Year is Leap Year or Not

Take a few dates from different years, as shown in the below screenshot.

year example 4-1

We need to find which date belongs to the leap year from the above data. Follow the below steps to find the leap year using the YEAR function.

Start the Formula with the MONTH Function.

year example 4-2

Instead of inputting the serial number, input the DATE function, as shown in the below screenshot.

year example 4-3

Instead of inputting the year directly, use the YEAR function and choose the cell with the date.

year example 4-4

For the “MONTH” argument, input 2, and for the “DAY” argument, input 29.

year example 4-5

Close the DATE and MONTH brackets, then input equal to “=” 2 as shown below.

year example 4-6

Press Enter Key.

year example 4-7

Drag the Same Formula to other cells.

year example 4-8

Conclusion

From all the dates, we want to know whether February has 29 days or not for that year; February is the second month, so the formula “Month” function should return 2. It will return 2 only when the Date function is correct; that means if that year February has 28 days, it will scroll to March 1st and return 3, so the result of the Month formula will not match with the number 2.

Whenever February has 29 days, it will return 2, which will match the right-hand side 2 and return “True”.

This is one way; otherwise, we can find it differently, as below.

1. Left-hand side calculates the date after February 28; if it is equal to the right-hand side 29, then it is a leap year.

2. Left-hand side calculates the date before March 1; if it is equal to the right-hand side 29, then it is a leap year.

Things to Remember About YEAR Formula in Excel

  • The year formula is helpful when we require the details of a year alone from a large bunch of data.
  • Never input the Date directly in the Year function as it will consider it as text and will return the error #NAME as it will consider only as text.
  • If you want to use the year function directly, take the help of the Date function and use it, then you will not get any error.
  • Another way of using it is to convert the date into number format and use that number in the serial number to get the 4-digit year.
  • If you took the year before 1900, you would get the error message #VALUE hence ensure you are using the YEAR function for the years after 1900.
  • Ensure the date format is correct because using a value greater than 12 in a month or greater than 31 in the day will throw an error.

Recommended Articles

This is a guide to the YEAR Formula in Excel. Here we discuss How to use the YEAR Formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. YEAR in Excel
  2. VBA Month
  3. Excel Month Function
  4. WEEKDAY Formula in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download YEAR Formula Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download YEAR Formula Excel Template

EDUCBA

डाउनलोड YEAR Formula Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW