YEAR function convert date to year in Excel

This tutorial explains the syntax and uses of the Excel YEAR function and provides formula examples to extract year from date, convert date to month and year, calculate age from the date of birth and determine leap years.

In a few recent posts, we have explored different ways to calculate dates and times in Excel and learned a variety of useful functions such as WEEKDAY, WEEKNUM, MONTH, and DAY. Today, we are going to focus on a bigger time unit and talk about calculating years in your Excel worksheets.

In this tutorial, you will learn:

YEAR function in Excel

The YEAR function in Excel returns a four-digit year corresponding to a given date as an integer from 1900 to 9999.

The syntax of the Excel YEAR function is as simple as it could possibly be:

YEAR(serial_number)

Where serial_number is any valid date of the year you want to find.

Excel YEAR formula

To make a YEAR formula in Excel, you can supply the source date in several ways.

Using the DATE function

The most reliable way to supply a date in Excel is using the DATE function.

For example, the following formula returns the year for 28 April, 2015:

=YEAR(DATE(2015, 4, 28))

As a serial number representing the date

In the internal Excel system, dates are stored as serial numbers beginning with 1 January 1900, which is stored as number 1. For more information on how dates are stored in Excel, please see Excel date format.

The 28 day of April, 2015 is stored as 42122, so you can enter this number directly in the formula:

=YEAR(42122)

Though acceptable, this method is not recommended because date numbering may vary across different systems.

As a cell reference

Assuming you have a valid date in some cell, you can simply refer to that cell. For example:

=YEAR(A1)

As a result of some other formula

For example, you can use the TODAY() function to extract the year from the current date:

=YEAR(TODAY())

As text

In a simple case, the YEAR formula can even understand dates entered as text, like this:

=YEAR("28-Apr-2015")

When using this method, please double check that you enter the date in the format that Excel understands. Also, please remember that Microsoft does not guarantee correct results when a date is supplied as a text value.

The following screenshot demonstrates all of the above YEAR formulas in action, all returning 2015 as you might expect :)
Using the YEAR function in Excel

How to convert date to year in Excel

When you work with date information in Excel, your worksheets usually display full dates, including month, day and year. However, for major milestones and important events such as product launches or asset acquisitions, you may want to view only the year without re-entering or modifying the original data. Below, you will find 3 quick ways to do this.

Example 1. Extract a year from date using the YEAR function

In fact, you already know how to use the YEAR function in Excel to convert a date to a year. The screenshot above demonstrates a bunch of formulas, and you can see a few more examples in the screenshot below. Notice that the YEAR function perfectly understands dates in all possible formats:
Excel YEAR formula examples

Example 2. Convert date to month and year in Excel

To convert a given date to year and month, you can use the TEXT function to extract each unit individually, and then concatenate those functions within one formula.

In the TEXT function, you can use different codes for months and years, such as:

  • "mmm" - abbreviated months names, as Jan - Dec.
  • "mmmm" - full month names, as January - December.
  • "yy" - 2-digit years
  • "yyyy" - 4-digit years

To make the output better readable, you can separate the codes with a comma, hyphen or any other character, like in the following Date to Month and Year formulas:

=TEXT(B2, "mmmm") & ", " & TEXT(B2, "yyyy")

Or

=TEXT(B2, "mmm") & "-" & TEXT(B2, "yy")

Where B2 is a cell containing a date.
TEXT formulas to convert date to month and year in Excel

Example 3. Display a date as a year

If it does not really matter how the dates are stored in your workbook, you can get Excel to show only the years without changing the original dates. In other words, you can have full dates stored in cells, but only the years displayed.

In this case, no formula is needed. You just open the Format Cells dialog by pressing Ctrl + 1, select the Custom category on the Number tab, and enter one of the below codes in the Type box:

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

Displaying a date as a year

Please remember that this method does not change the original date, it only changes the way the date is displayed in your worksheet. If you refer to such cells in your formulas, Microsoft Excel will perform date calculations rather than year calculations.

You can find more details about changing the date format in this tutorial: How to change date format in Excel.

How to calculate age from date of birth in Excel

There are several ways to calculate age form date of birth in Excel - using DATEDIF, YEARFRAC or INT function in combination with TODAY(). The TODAY function supplies the date to calculate age at, ensuring that your formula will always return the correct age.

Calculate age from date of birth in years

The traditional way to calculate a person's age in years is to subtract the birth date from the current date. This approach works fine in everyday life, but an analogous Excel age calculation formula is not perfectly true:

INT((TODAY()-DOB)/365)

Where DOB is the date of birth.

The first part of the formula (TODAY()-B2) calculates the difference is days, and you divide it by 365 to get the number of years. In most cases, the result of this equation is a decimal number, and you have the INT function round it down to the nearest integer.

Assuming the date of birth is in cell B2, the complete formula goes as follows:

=INT((TODAY()-B2)/365)
A formula to convert a date of birth to age

As mentioned above, this age calculation formula is not always flawless, and here's why. Every 4th year is a leap year that contains 366 days, whereas the formula divides the number of days by 365. So, if someone was born on February 29 and today is February 28, this age formula will make a person one day older.

Dividing by 365.25 instead of 365 is not impeccable either, for example, when calculating the age of a child who hasn't yet lived through a leap year.

Given the above, you'd better save this way of calculating age for normal life, and use one of the following formulas to calculate age from date of birth in Excel.

DATEDIF(DOB, TODAY(), "y")
ROUNDDOWN(YEARFRAC(DOB, TODAY(), 1), 0)

The detailed explanation of the above formulas is provided in How to calculate age in Excel. And the following screenshot demonstrates a real-life age calculation formula in action:

=DATEDIF(B2, TODAY(), "y")
A formula to calculate age from date of birth in years

Calculating exact age from date of birth (in years, month and days)

To calculate an exact age in years, months and days, write three DATEDIF functions with the following units in the last argument:

  • Y - to calculate the number of complete years.
  • YM - to get the difference between the months, ignoring years.
  • MD - to get the difference between the days, ignoring years and months.

And then, concatenate the 3 DATEDIF functions in a single formula, separate the numbers returned by each function with commas, and define what each number means.

Assuming the date of birth is in cell B2, the complete formula goes as follows:

=DATEDIF(B2,TODAY(),"Y") & " Years, " & DATEDIF(B2,TODAY(),"YM") & " Months, " & DATEDIF(B2,TODAY(),"MD") & " Days"

This age formula may come in very handy, say, for a doctor to display the exact age of patients, or for a personnel officer to know the exact age of all employees:
Calculating exact age from date of birth in Excel

For more formula examples such as calculating age at a particular date or in a certain year, please check out the following tutorial: How to calculate age in Excel.

How to get the day number of the year (1-365)

This example demonstrates how you can get the number of a certain day in a year, between 1 and 365 (1-366 in leap years) with January 1 considered day 1.

For this, use the YEAR function together with DATE in this way:

=A2-DATE(YEAR(A2), 1, 0)

Where A2 is a cell containing the date.
The DATE / YEAR formula to get the day number in a year

And now, let's see what the formula actually does. The YEAR function retrieves the year of the date in cell A2, and passes it to the DATE(year, month, day) function, which returns the sequential number that represents a certain date.

So, in our formula, year is extracted from the original date (A2), month is 1 (January) and day is 0. In fact, a zero day forces Excel to return December 31 of the previous year, because we want January 1 to be treated as the 1st day. And then, you subtract the serial number returned by the DATE formula from the original date (which is also stored as a serial number in Excel) and the difference is the day of the year you are looking for. For example, January 5, 2015 is stored as 42009 and December 31, 2014 is 42004, so 42009 - 42004 = 5.

If the concept of day 0 does not seem right to you, you can use the following formula instead:

=A2-DATE(YEAR(A2), 1, 1)+1

How to calculate the number of days remaining in the year

To compute the number of days remaining in the year, we are going to use the DATE and YEAR functions again. The formula is based on the same approach as Example 3 above, so you are unlikely to have any difficulties with understanding its logic:

=DATE(YEAR(A2),12,31)-A2
Calculating the number of days remaining in the year

If you want to know how many days remain till the end of the year based on the current date, you use the Excel TODAY() function, as follows:

=DATE(2015, 12, 31)-TODAY()

Where 2015 is the current year.

Calculating leap years in Excel

As you know, nearly every 4th year has an extra day on February 29 and is called a leap year. In Microsoft Excel sheets, you can determine whether a certain date belongs to a leap year or a common year in a variety of ways. I'm going to demonstrate just a couple of formulas, which in my opinion are easiest to understand.

Formula 1. Check if February has 29 days

This is a very obvious test. Since February has 29 days in leap years, we calculate the number of days in month 2 of a given year and compare it with number 29. For example:

=DAY(DATE(2015,3,1)-1)=29

In this formula, the DATE(2015,3,1) function returns the 1st day of March in the year 2015, from which we subtract 1. The DAY function extracts the day number from this date, and we compare that number with 29. If the numbers match, the formula returns TRUE, FALSE otherwise.

If you already have a list of dates in your Excel worksheet and you want to know which ones are leap years, then incorporate the YEAR function in the formula to extract a year from a date:

=DAY(DATE(YEAR(A2),3,1)-1)=29

Where A2 is a cell containing the date.

The results returned by the formula are as follows:
Calculating leap years in Excel

Alternatively, you can use the EOMONTH function to return the last day in February, and compare that number with 29:

=DAY(EOMONTH(DATE(YEAR(A2),2,1),0))=29

To make the formula more user-friendly, employ the IF function and have it return, say, "Leap year" and "Common year" instead of TRUE and FALSE:

=IF(DAY(DATE(YEAR(A2),3,1)-1)=29, "Leap year", "Common year")

=IF(DAY(EOMONTH(DATE(YEAR(A2),2,1),0))=29, "Leap year", "Common year")
Use the IF function to make the leap year formula more user-friendly

Formula 2. Check if the year has 366 days

This is another obvious test that hardly requires any explanation. We use one DATE function to return 1-Jan of the next year, another DATE function to get 1-Jan of this year, subtract the latter from the former and check if the difference equals to 366:

=DATE(2016,1,1) - DATE(2015,1,1)=366

To calculate a year based on a date entered in some cell, you use the Excel YEAR function exactly in the same way as we did in the previous example:

=DATE(YEAR(A2)+1,1,1) - DATE(YEAR(A2),1,1)=366

Where A2 is a cell containing the date.

And naturally, you can enclose the above DATE / YEAR formula in the IF function for it to return something more meaningful than the Boolean values of TRUE and FALSE:

=IF(DATE(YEAR(A2)+1,1,1) - DATE(YEAR(A2),1,1)=366, "Leap year", "Non-leap year")
Another way to determine leap and non-leap years in Excel

As already mentioned, these are not the only possible ways to calculate leap years in Excel. If you are curious to know other solutions, you can check the method suggested by Microsoft. As usual, Microsoft guys are not looking for easy ways, are they?

Hopefully, this article has helped you figure out year calculations in Excel. I thank you for reading and look forward to seeing you next week.

151 comments

  1. Hello,
    I want to know the formula how can i get the remaining time of Service if the retirement total period is 62 years

  2. Ple help one boy asking ple help name: Sanjay date of birth:6:12:2000,now what is age now ple help me

  3. I need to find out what year someone was 65 years old
    So I have a column with DOB DD/MM/YYY and I need a calculation to tell me YYYY they turned 65.

  4. Thanks very much for useful infornation

  5. Dear Madam

    In my case the employee of company are working earlier than 2000, but a policy was introduced in 2009 by company in which employee who is working earlier than 2000 can get his 1st salary increment of 15% of basic pay after 2 years i.e. 2011 and the next increment of 10% of basic pay in the third year i.e. 2012.
    Please help me I stuck hard in this position

    Regards

  6. Trying to calculate the age of a student at the time they participated in a program. I have the date of birth and I have the year of the program in which they participated. Some students participated in multiple programs in various year.

    Example:

    Name Date of Birth Program Year
    Larry Johnson 3/21/1982 College Scholarship 1999
    Tyler Scott 12/01/1986 STEM Camp 2000
    Tyler Scott 12/01/1986 College Scholarship 2001

    Is there one formula I can use to calculate the estimated age at the time the student participated in the program?

    • Hello Crystal,

      Supposing, the DOF is in column B, and the year of the participation in the program is in column D, you can use the following formula:

      =DATEDIF(B2,DATE(D2, 1, 1),"y")

      Because the exact date of the program is not defined, you can supply the date and month that you think are the most appropriate, like January 1 in the above example: DATE(D2, 1, 1)

  7. I have a spreadsheet that lists the employees start date. I am trying to separate into bands- 0-6M, 6M-1Y, 1Y-3Y,3Y-5Y,5Y+. Any ideas? I can add columns, then sort by, but this is a large organization, and I'm trying to automate as much as possible.

    Thanks, everyone!

  8. Hi. I'd like to display the difference between two reading test ages as a negative if a pupil's results have deteriorated and as a positive if they have made progress.

    For example, the positive difference between: 06 Year(s), 05 Month(s) and 06 Year(s), 11 Month(s) (after the next test).

    Thanks in advance! Michelle

  9. Hi dear,
    How to calculate age from this format that
    1day in cell a1, January in cell a2, and 1992 in cell a3 plz solve this.

    • Hello Rahman,

      Please use
      =DATEDIF(DATE(A3,MONTH(DATEVALUE(A2&"1")),A1),TODAY(),"y") & " Years, " & DATEDIF(DATE(A3,MONTH(DATEVALUE(A2&"1")),A1),TODAY(),"ym") & " Months, " & DATEDIF(DATE(A3,MONTH(DATEVALUE(A2&"1")),A1),TODAY(),"md") & " Days"

  10. Hello,

    How do I find the effective and expiration dates using a date
    For Ex: I have a loss date as 6/2/2015 and my effective date is 10/1/2015 and expiration dated is 10/1/2016

  11. Please comment. Its urgent. I think Svetlana Cheusheva can solve it.

    • Hello Ramteke,

      If my understanding of the task is correct, you just need to add 55 years to the date of birth.

      If so, please use this formula, where A2 is the date of birth:
      =DATE(YEAR(A2) + 55, MONTH(A2), DAY(A2))

  12. Suppose Date of Birth of a person is 07/12/1962. I want to calculate on what date he completes his 55 years of age.

  13. Hi, can you please help me ?

    Let's say I have 11 tables with different amount.
    11
    4466
    568
    789
    356
    567 suppose if this are days, how do we calculate the avarage in years ?

  14. Hi there,

    I am trying to calculate age from year of birth only (not DOB). It seems to work using simple subtraction however when I click and drag the bottom right corner of the highlighted area it just copies and pastes the contents of the cell rather than using the subtraction formula for each cell in the age column. How do I correct this as I have a lot of data?

    Best wishes
    Blaithin

  15. CAN I USE THE FORMULA =YEARFRAC(N141,O141,1)*12 TO CONVERT MONTHS AND DAYS TO DECIMAL?
    EXAMPLE
    5/14/2015 12/31/2015 7 months, 17 days 7.59

  16. Dear madam!
    i need your help

    if i type the year 2013 then how count in number 3 year

  17. Good day,
    I have a worksheet with drop down list for day, month and year in separate cells. I want a formula to calculate and display the age in the next cell. I tried a lot, but it is not calculating from the drop down list. Please help me.

  18. How can I know 58 year over age from date of birth

  19. Hi I need to know how to convert something like, 48 years and 2 months into years. Would really appreciate an answer, thanks.

  20. I need to calculate years of service from the start date to 12/31 of the current year, sort of like the today function, but it will be a set date of 12/31 and the year will change every year.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)