Comments on: 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. Continue reading →

Comments page 2. Total comments: 151

  1. Can some one help me to hide values in zero if I use this formula to calculate employment tenure in organization.
    Example.
    I get the result 0 years 0 months 20 days.
    I want to show it as 20 days.

    Regards.

    1. Hello!
      The information you provided is not enough to understand your case and give you any advice, sorry.
      What formula you used? Give an example of the source data.
      It’ll help me understand it better and find a solution for you.

      1. I used the mentioned below formula to calculate employment tenure in years months and days.

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

  2. I'd like to calculate end-dates for financial purposes.

    E.g., a security with a 1 year maturity period will mature on 31/12/2020, if the investment was made on 01/01/2020.

    DATE formulae in Excel that I have seen so far seem to reflect the anniversary of the start date, i.e., 1 year from 01/01/2020 is given as 01/01/2021 (and not 31/12/2020) and 5 years from 01/01/2020 is given as 01/01/2025 (and not 31/12/2024, which is the correct date).

    Is there any way to address this?

  3. I have list of employees with their date of birth (in dd-mmm-yyyy format) n a worksheet.
    I want to highlight the date of birth in rows which are unique. ie., just the date & month when they are unique ignoring the year. eg., there may be two employees, 1 with date of birth 2-Jun-1993 & another with date of birth 2-Jun-1975. So, i want these two to be highlighted ignoring the year.
    Can you please help me how to do it either using a formula or conditional formatting.
    Thanks in advance.

    1. Hello!
      I recommend using an extra column. Write down the dates of birth with the same year in it. You can use something like this:

      =DATE(2000,MONTH(A1),DAY(A1))

      In this column use conditional formatting - Highlight cell rules - Duplicate Values

  4. Hello,
    can you please help to create a formula that can be use
    i want to determine and the remarks appear based on below in one cell only
    if date is less than 6 months from now = SLOWLY MOVING
    if date is less than 1 year from now = NON MOVING
    if date is less than 3 months = RUNNING

  5. I only have a data of year and number of day in a year. Example Year is 2020 and the number of day in the year is 32 then the result should be Feb 2, 2020. Is there a formula which I can obtain this result?

    1. Correction: the result should be Feb 1, 2020

  6. Would appreciate if you give provide a formula for below scenario. Thank you in advance.
    Start date: 5-27-2013
    End Date: 10-3-2019
    Counting number of years using a fraction of at least six (6)months being considered as one (1) whole year. So, 5-27-2013 to 12-31-2013, is considered one (1) year. Counting on a yearly basis, it means that from 5-27-2013 to 10-3-2019, is considered 7 years. Can you please provide me a formula for these?
    Regards,
    Felix

  7. Doj - 01-07-2005
    27-01-2013
    03-01-2000
    cut off date - 01-04.2019
    How many year experience (made hiffen in all dates)
    Thanks in advance
    Manjunath

  8. mere pass age ka column hai....date of year define karna hai kaise hoga..
    example
    srno name age dob( dd-mm-yyyy)
    1 aaa 42
    2 bbbb 55
    3 ccc 65
    4 ddd 32

  9. how can I use if function to calculate the YEAR column from a given date column? Example :I HAVE A COLUMN
    23/06/2018
    13/07/2017
    6/08/2001
    24/10/2006
    GENERATE ANOTHER COLUMN FOR YEAR USING IF FUNCTION ?
    I NEED THE FORMULAR PLS?
    THANKS

  10. Hello
    I need to calculate the number of days that is in each month that regards to the Start date and End date.
    Fx. Des 18 (zero), Jan 19 (zero), feb 19 (13 days) , mar 19 (31 days) and so on

    Start date ---- End date des.18 jan.19 feb.19 mar.19 apr.19 mai.19 jun.19
    15.02.2019 18.10.2019

  11. Hi, I hope you can help me with the correct formula to calculate separation pay. What is the formula to show that a fraction of at least six (6) months shall be considered as one (1) whole year.

    Start Date: 02/08/1995
    Separation Date: 09/05/2018
    Years of Service: 23 years and 6 months

    How can I get 24 years total years of service since 6 months should be considered an additional year? Thankful for your help in advance.

  12. I was wondering how i can add year to date to an excel sheet gor payroll i have no current year to date on the spreadsheet right now?

    1. April:
      I think you're asking how to create a running total. This is where the latest data is added to the existing total to display the most current total. This procedure is repeated each time data is added so that the sheet shows the newest data and the newest total.
      If this is what you want to do, you create a running total by entering a range formula which is part absolute reference and part relative reference. The range formula looks like this: $A$1:A1. Absolute reference with the "$", relative reference without it. Where A1 is the first cell in the range.
      So, if the cell that will hold the payroll to be used in the Year-to-Date amount is in column "D" and the calculated running total is in column "E" the formula in column "E" will look like this:
      =SUM($D$6:D6) formula in E6 displays Year-to-Date of amounts in cells above E6
      =SUM($D$6:D7) formula in E6 displays Year-to-Date of amounts in cells above E7
      =SUM($D$6:D8) formula in E6 displays Year-to-Date of amounts in cells above E8
      After you've copied it down to row 6, 7 & 8. This will calculate the running total to row 8. Just continue to copy this formula down the "E" column to display the most current Year-to-Date.

  13. How can i add year to date for payroll?

  14. Hi

    Please help!!

    I have tenure as 17.5 years, need to know the joining date from today what formula I should use.

    Please advise?

    Regards,
    Ashish

  15. Hi ,

    If I have 13/05/2014 date and I want to convert to Financial year April to March how do I do?
    Thanks.

  16. Hello experts! I wish to calculate a student's current grade in school based on their graduation year. The trick is that students' grade levels change as of July 1st each year. Examples: A student's graduation year is 2021. Today is May 25, 2018. They are currently in 9th grade, but as of July 1st, 2018 will be in 10th grade. Perhaps it would be useful to assume that "graduation year X" really equals "June 30, year X"?

    Here are the graduation years and corresponding grade levels through June 30th. As of July 1st, the grade level will need to rise by 1.

    2029 - 1
    2028 - 2
    2027 - 3
    2026 - 4
    2025 - 5
    2024 - 6
    2023 - 7
    2022 - 8
    2021 - 9
    2020 - 10
    2019 - 11
    2018 - 12
    >2018 - grad

    Further, could the formula cause the cell to be shaded yellow if the grade is 1-5, green if grade 6-8, blue if grade 9-12, and pink if grad?

    It would also be useful to be able to do the opposite - calculate a grad year based on their current grade in school.

    I know you must enjoy a good puzzle!

    Many thanks.
    Stuart R

  17. Hi,

    I want to know how to calculate retirement age remaining years in negative value means if employees already cross the retirement date so how they show in reaming year in negative value.

    Please help it's urgent.

  18. Hi All,

    I have one query regarding my data I have a cell-like 05;11;18 and where 05, 11 and 18 are the date so how can I extract and count it as 3.

  19. Hi All,

    How can I add year fraction in excel. for e.g. I have 1st company experience as 2.6 (2 years 6 months) and next company experience is 16.10 (16 years 10 months) if I am adding both ideally my total experience is 19.4 (19 years 4 months) whereas if I am trying to calculate in excel, outcome is 18.76.

    Please advise if I could use any alternate formula to sum up year fraction in excel.

    Regards,
    Vikram

    1. Hello,

      Please try the following formula:

      =(2+16+TRUNC((6+10)/12)) &" years " & ((6+10)-(12*TRUNC((6+10)/12))) & " months"

      Hope it will help you.

  20. Hi,

    I want to calculate service period in three different cell at excel. Ex- Suppose someone's service year is 05 Years, 10 Months & 09 days. I want to show above three result in three different cell. Is it possible? if possible please reply me. I am real big trouble.

    Thanks
    Asfaq

  21. What is the formula I should use when trying to find the total number of entries for a specific year? See Below:
    First Name Rejection Date
    Laura 12/17/2013
    Veronica 12/19/2013
    Jonathan 1/23/2014
    Erin 2/1/2014
    Lucinda 2/24/2015
    Rosa 3/14/2015
    Bradley 7/2/2016
    Maria 7/30/2016
    David 8/7/2017
    Cheryl 8/7/2017
    Paige 8/7/2017

    I am trying to figure out how many people were "rejected" in 2013, 2014, 2015, etc. without having to create numerous columns, etc.

    Thank you for your help.

    1. Hi, Megan,

      if you have an additional status column saying who was rejected, you can use COUNTIFS function. Please read this article to learn more about the COUNTIFS syntax and to be able to use it.

  22. I want to get the length of service of my manpower. Is there a formula for this in excel? So that I could identify the number of years of my manpower. Thank you

  23. Thanks, this helped me a lot.
    Regards,
    Js

  24. Hi
    Could you please help me to change the year format.
    how to change 10-mar-40 to 10-mar-2040 in excel

    1. Hi,

      After you entered the date (make sure to enter the complete year - 2040, not just 40), select the cell and press Ctrl+1 to open the Format Cells window. There, go to Date tab and find the following data type: 14-Mar-2012. Select it and press OK.

      Hope it helps!

  25. Hi
    Help!
    How can I exit 35 months in Excel?

    1/1/1988 TO 31/121988
    1/1/1989 TO 31/12/1989
    1/1/1990 TO 30/11/1990

  26. Hi
    Help!
    aHow can I exit 35 months in Excel?

    1/1/1988 TO 31/121988
    1/1/1989 TO 31/12/1989
    1/1/1990 TO 30/11/1990

  27. Formula for calculating when a person will be 17 years and 6 months old?

  28. using the formula =DATEDIF(A2,NOW(),"y") gets me the age for A2. how do I put that formula into 300 rows with out having to change the formula each it each time i.e:
    =DATEDIF(A3,NOW(),"y")
    =DATEDIF(A4,NOW(),"y")
    =DATEDIF(A5,NOW(),"y")
    etc.

    Thanks

  29. Hi
    Help!
    I need a formula that will calculate the number of days in the year over a period of time, not from January to December, that will take into account leap years. For example a date of contract starts 01/02/2016 and ends 31/01/2017 = 366 days. But, a contract that starts 01/04/2016 and ends 31/03/2017 is 365 days. How do I create a formula that will take this into consideration across several years and lines of data?
    Thanks :)

  30. I want to calculate Date of Birth of 25 years 4 months 12 days to date/month/year(ex.01/01/2017) format, kindly help, thanks.

  31. trying to figure out the date. if age 18 or more at time of exam, then purge date is 7 years from date of exam.

    If under 18 on date of exam, then purge date is 7 years from 18th birthday.

    I Have one student
    DOB 10/2/1985
    Exam 03/07/04

    Second Student
    11/01/1960
    06/12/78

    and about 700 other students. how do I find the purge date?

    1. DOB Exam Date Age@exam Purge date
      11/01/60 06/12/1978 17 ?

  32. Hi
    Could you please help me to collect data from age.

    I used =DATEDIF(actualdata,NOW(),"y") and I got everyone age.

    Now I want to see only:
    - over 65 years old -
    and
    - under 16-

    I am trying quickly recognise who is under 16 and over 65 from my list and how many people is under 16 and how many people is over 65.

    I be thankful if you show me the way how to this.

    Thank you

  33. How do I take a cell with a date in it and convert it to years using a specific date not TODAY as a date. Example I need 06/01/1996 to show me how many years they have been working for us from hired date through 12.31.2016.

  34. If I get to know the year.. I can 1st Jan and their birth year for all the employees Those who have not shared their date of birth

  35. I have only a age for 300 employees, like 30 yers 20 yers and 40 yrs etc.. then how do I calculate their birth year in excel..

    It will be a great help if you could provide me a formula on this..

  36. Hi,

    My query quite simple..

    I have only a age for 300 employees, like 30 yes 20 yes and 40 yes then how do I calculate their birth year in excel..

    It will be a great help if you could provide me a formula on this..

  37. Thank you!

  38. Hi, I kept having shortage of year. For example:
    Start 15/12/2014
    Expiration 14/12/2016
    Period 23 months

    Where I should get 24 months.

    Please assist.

  39. Hi Svetlana, I have a column that is using the format m/d/yyyy h:mm. How do I isolate the year?

  40. Re: Calculating leap years...

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

    ...can be simplified to

    =DAY(DATE(YEAR(A2),3,0))=29

    No need to subtract one, or use EOMONTH, as the DATE function already treats the Year/Month/Day arguments as offsets, not absolute values.

  41. Hi!
    Thanks for the amazing post; it helped a lot.

    I just have one question I'm trying to calculate following information which I got using DATEDIF

    0 Years, 8 Months, 0 Days
    4 Years, 3 Months, 0 Days
    1 Years, 9 Months, 0 Days
    1 Years, 1 Months, 0 Days

    Thanks in advance

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

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

  44. 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.

  45. Thanks very much for useful infornation

  46. 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

  47. 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?

    1. 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)

  48. 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!

  49. 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

  50. 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.

    1. 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"

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 :)